Tracking occurrence dates and reporting on them

[#1]

Hi,

I've created a daily incremental extract but I need to validate and track some occurrence IDs.

So my data is in this format:

Date	ID
20/5/2017	7526-9856
20/5/2017	4523-1826
19/5/2017	6352-4736
19/5/2017	4545-5236
19/5/2017	4523-1826
18/5/2017	4523-1826

I'd like to be able to calculate 3 things:

1. On today's date, how many occurrences are pre-existing and how many are new
2. The number of days each occurrence has existed
3. The average number of days an occurrence existed for

So a table that looks like this:

Date	Total No of Occurances	Pre-Existing Occurances	New Occurances
20/5/2017	2	1	1
19/5/2017	3	1	2
18/5/2017	1	0	1

And another table that looks like:

ID	Days Existing
7526-9856	1
4523-1826	3
6352-4736	1
4545-5236	1

I would then be able to work out the last one from the second table above.

Thanks....

[antoniotiman]

Hi,

see Attachment.

Regards,

Antonio

[antoniotiman · Accepted solution]

HI,

Temp:
LOAD Date, 
ID  
FROM
"https://community.qlik.com/message/1289550"
(html, codepage is 1252, embedded labels, table is @1);

LOAD *,If(ID=Peek(ID),1,0) as Flag Resident Temp Order By ID;
Drop Table Temp;