how to find a new ID?

[Anonymous]

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.

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

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

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

[sunny_talwar]

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

[Anonymous]

in the script

[sunny_talwar · Accepted solution]

Is this what you want?

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:

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]

exactly what I am looking for! thanks!

[Anonymous]

thanks