Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to find a new ID?

i am analyzing the growth a hospital and I have the date and person_id that tell me who and when visited the hospital.

I want to identify who are the new patients (person_id) in each period, looking back 3 years.

The data format looks like this. I know it has to be a loop, can anyone help me?!! Many Thanks.

  

DOSPerson_ID
2017-09-2293677
2017-07-25131888
2017-08-01131888
2017-07-2547025
2017-07-2847038
2017-08-0447301
2017-08-04115418
2017-08-0847261
2017-08-1147128
2017-08-1647030
2017-08-1847301
2017-08-1847161
2017-08-2246977
2017-08-2547030
2017-09-0147027
2017-09-0747239
2017-09-0847375
2017-09-1147038
2017-09-13118697
2017-09-1547025
2017-09-25118697
2017-09-2965605
2017-10-02118697
2017-10-0647028
2017-10-09118697
2017-10-1347025
2017-10-13109642

.......................

..............

1 Solution

Accepted Solutions
sunny_talwar

Is this what you want?

Capture.PNG

Table:

LOAD *,

Year(DOS) as YearDOS;

LOAD * INLINE [

    DOS, Person_ID

    2017-09-22, 93677

    2017-07-25, 131888

    2017-08-01, 131888

    2017-07-25, 47025

    2017-07-28, 47038

    2017-08-04, 47301

    2017-08-04, 115418

    2017-08-08, 47261

    2017-08-11, 47128

    2017-08-16, 47030

    2017-08-18, 47301

    2017-08-18, 47161

    2017-08-22, 46977

    2017-08-25, 47030

    2017-09-01, 47027

    2017-09-07, 47239

    2017-09-08, 47375

    2017-09-11, 47038

    2017-09-13, 118697

    2017-09-15, 47025

    2017-09-25, 118697

    2017-09-29, 65605

    2017-10-02, 118697

    2017-10-06, 47028

    2017-10-09, 118697

    2017-10-13, 47025

    2017-10-13, 109642

];


FinalTable:

LOAD *,

If(Person_ID <> Previous(Person_ID), 'New',

If(YearDOS >= Previous(YearDOS) + 3, 'New', 'Existing')) as Flag

Resident Table

Order By Person_ID, DOS;


DROP Table Table;

View solution in original post

6 Replies
sunny_talwar

In the script or front end? For script, you can use Peek/Previous

Anonymous
Not applicable
Author

in the script

sunny_talwar

Is this what you want?

Capture.PNG

Table:

LOAD *,

Year(DOS) as YearDOS;

LOAD * INLINE [

    DOS, Person_ID

    2017-09-22, 93677

    2017-07-25, 131888

    2017-08-01, 131888

    2017-07-25, 47025

    2017-07-28, 47038

    2017-08-04, 47301

    2017-08-04, 115418

    2017-08-08, 47261

    2017-08-11, 47128

    2017-08-16, 47030

    2017-08-18, 47301

    2017-08-18, 47161

    2017-08-22, 46977

    2017-08-25, 47030

    2017-09-01, 47027

    2017-09-07, 47239

    2017-09-08, 47375

    2017-09-11, 47038

    2017-09-13, 118697

    2017-09-15, 47025

    2017-09-25, 118697

    2017-09-29, 65605

    2017-10-02, 118697

    2017-10-06, 47028

    2017-10-09, 118697

    2017-10-13, 47025

    2017-10-13, 109642

];


FinalTable:

LOAD *,

If(Person_ID <> Previous(Person_ID), 'New',

If(YearDOS >= Previous(YearDOS) + 3, 'New', 'Existing')) as Flag

Resident Table

Order By Person_ID, DOS;


DROP Table Table;

MarcoWedel

Hi,

one solution might be as well:

QlikCommunity_Thread_296266_Pic1.JPG

QlikCommunity_Thread_296266_Pic2.JPG

tabTemp:

LOAD * FROM [https://community.qlik.com/thread/296266] (html, codepage is 1252, embedded labels, table is @1);

tabPatients:

LOAD *,

    Person_ID as TempField,

    1+Exists(TempField,Person_ID) as IsNewPatient

Resident tabTemp

Order By DOS;

DROP Table tabTemp;

DROP Field TempField;

hope this helps

reagrds

Marco

Anonymous
Not applicable
Author

exactly what I am looking for! thanks!

Anonymous
Not applicable
Author

thanks