Need to compare Data between weeks

[#1]

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

[MK_QSL · Accepted solution]

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

[datanibbler]

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