How to loop over each id in qlik sense to get a minimum and maximum of his related data

[im-officer]

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.

[Saryk]

Try with 

Min(Num(Date([test_date])))

[im-officer]

It returned all the rows available for each patient not the row with the minimum date.

[qlikerB96]

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.