topic Re: Need to compare Data between weeks in QlikView

Need to compare Data between weeks

Wed, 24 Sep 2014 12:49:45 GMT

Hi,

I have a table where there are two columns, 1st column is week no and second is patientid. I need to find the latest week patientid which is not matching with previous weeks patient id.

Ex:

Week Patientid

12 Patient1

12 Patient2

12 Patient3

12 Patient4

12 Patient5

12 Patient6

12 Patient7

13 Patient1

13 Patient3

13 Patient4

13 Patient5

13 Patient7

13 Patient8

I need retrieve PatientID = Patient8,Patient2,Patient6

Thanks in Advance

Re: Need to compare Data between weeks

MK_QSL — Wed, 24 Sep 2014 13:00:37 GMT

=Concat({<Week = {'$(=Max(Week))'}, Patientid = e({1<Week = {'$(=Max(Week)-1)'}>})> + <Week = {'$(=Max(Week)-1)'}, Patientid = e({1<Week = {'$(=Max(Week))'}>})>}DISTINCT Patientid,', ')

Re: Need to compare Data between weeks

datanibbler — Wed, 24 Sep 2014 13:02:10 GMT

Easy:

- You need a RESIDENT LOAD and an ORDER BY clause to sort by patient_ID (primary) and week_no (secondary). (only possible in a RESIDENT)

- Then you use the PREVIOUS() function to compare whether the patient_ID is the same as in the record above (which should, if that is the case, hold the week_no of the prior week)

HTH

Best regards,

DataNibbler