Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
im-officer
Contributor
Contributor

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

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.

Labels (2)
3 Replies
Saryk
Partner
Partner

Try with 

Min(Num(Date([test_date])))
im-officer
Contributor
Contributor
Author

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

qlikerB96
Contributor III
Contributor III

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.