Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Tracking occurrence dates and reporting on them

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:

DateID
20/5/20177526-9856
20/5/20174523-1826
19/5/20176352-4736
19/5/20174545-5236
19/5/20174523-1826
18/5/20174523-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:

DateTotal No of OccurancesPre-Existing OccurancesNew Occurances
20/5/2017211
19/5/2017312
18/5/2017101

And another table that looks like:

IDDays Existing
7526-98561
4523-18263
6352-47361
4545-52361

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

Thanks....

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Tracking occurrence dates and reporting on them

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;

2 Replies
antoniotiman
Honored Contributor III

Re: Tracking occurrence dates and reporting on them

Hi,

see Attachment.

Regards,

Antonio

antoniotiman
Honored Contributor III

Re: Tracking occurrence dates and reporting on them

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;