Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table full of patients visits and record and need to load on qlik sense. But what I want is to create a table that take each patient id and do the following:
1. Take the minimum date (first visit) and the related test result of it;
2. Take the maximum date (last visit) and its related test result;
3. Then display a table having: patient id, min date, min date test result, max date, max date result.
I tried the following:
LOAD Min(Date([test_date])) as mindate,
test_result,
beneficiary_id
//Max(Date([test_date])) as maxdate,
Resident [full_table] group by beneficiary_id;
And the same for maximum date, but the table took the same min and max for all patients.
Try with
Min(Num(Date([test_date])))
It returned all the rows available for each patient not the row with the minimum date.
Hi,
If you use group by you have to group for all fileds or use an aggregation expression.
You should try this:
LOAD
test_result,
beneficiary_id,
Min(Date([test_date])) as mindate,
Max(Date([test_date])) as maxdate
Resident [full_table] group by beneficiary_id, test_result;
Good luck,
B.