Merge and aggregate datasets: Difference between revisions
Content added Content deleted
(Harbour: use a value of -999 for missing scores) |
|||
| Line 798: | Line 798: | ||
=={{header|Harbour}}== |
=={{header|Harbour}}== |
||
Harbour does not have special values for NA or NaN, and missing numerical values are represented as zeros. In the following, we have used -999 for missing scores. |
|||
<syntaxhighlight lang="xbase"> |
<syntaxhighlight lang="xbase"> |
||
&& SQL-like INSERT command (note the variable number of arguments) |
|||
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUE ( <uVal1>[, <uValN> ] ) => ; |
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUE ( <uVal1>[, <uValN> ] ) => ; |
||
<table>->(dbAppend()); <table>-><uField1> := <uVal1> [; <table>-><uFieldN> := <uValN>] |
<table>->(dbAppend()); <table>-><uField1> := <uVal1> [; <table>-><uFieldN> := <uValN>] |
||
| Line 827: | Line 828: | ||
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}} |
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}} |
||
dbCreate( "visit", vStruct,, .T., "visit" ) |
dbCreate( "visit", vStruct,, .T., "visit" ) |
||
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8) |
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8), ; |
||
(1001, ctod("2020-09-17"), 5.5), (4004, ctod("2020-09-24"), 8.4), ; |
|||
(2002, ctod("2020-10-08"), -999), (1001, ctod("1900-01-01"), 6.6), ; |
|||
(3003, ctod("2020-11-12"), -999), (4004, ctod("2020-11-05"), 7.0), ; |
|||
| ⚫ | |||
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6) |
|||
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12")) |
|||
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11-05"), 7.0) |
|||
| ⚫ | |||
INDEX ON patient_id TO visit_id |
INDEX ON patient_id TO visit_id |
||
| Line 846: | Line 844: | ||
DO WHILE ! Eof() |
DO WHILE ! Eof() |
||
xCurId := patient_id && grouping variable |
xCurId := patient_id && grouping variable |
||
aAgg := {0, 0, 0.0, ctod(" |
aAgg := {0, 0, 0.0, ctod("1900-01-01")} && initial values |
||
DO WHILE ! Eof() .AND. xCurId == patient_id |
DO WHILE ! Eof() .AND. xCurId == patient_id |
||
aAgg := {1+aAgg[1], iif(score== |
aAgg := {1+aAgg[1], iif(score==-999,aAgg[2],1+aAgg[2]), ; |
||
iif(score==-999, aAgg[3], score+aAgg[3]), max(visit_date, aAgg[4])} && update |
|||
SKIP |
SKIP |
||
ENDDO |
ENDDO |
||