topic Generate missing data and flag data in App Development

Generate missing data and flag data

andrefpc — Thu, 11 Feb 2021 15:35:28 GMT

Hello community!

First of all I'm sorry if this is posted elsewhere and I'm just spamming.

I have a tricky development in hands...

I have a table in qlik with UserID, Type and EventDateTime.

Type refers to Entry or Exit. What I would like to have is a table with UserID, Date (from EventDateTime). Time (from EventDateTime) and a Flag which would have value 1 between Entry and Exit and 0 in the remaining hours of day.

Reality:

UserID	Type	EventDateTime
1	1	2021-02-01 10:00:00
1	2	2021-02-01 15:20:00

Desired Output:

UserID	Date	Time	Flag
1	01/02/2021	00:00:00	0
1	01/02/2021	01:00:00	0
1	01/02/2021	02:00:00	0
1	01/02/2021	03:00:00	0
1	01/02/2021	04:00:00	0
1	01/02/2021	05:00:00	0
1	01/02/2021	06:00:00	0
1	01/02/2021	07:00:00	0
1	01/02/2021	08:00:00	0
1	01/02/2021	09:00:00	0
1	01/02/2021	10:00:00	1
1	01/02/2021	11:00:00	1
1	01/02/2021	12:00:00	1
1	01/02/2021	13:00:00	1
1	01/02/2021	14:00:00	1
1	01/02/2021	15:00:00	1
1	01/02/2021	16:00:00	0
1	01/02/2021	17:00:00	0
1	01/02/2021	18:00:00	0
1	01/02/2021	19:00:00	0
1	01/02/2021	20:00:00	0
1	01/02/2021	21:00:00	0
1	01/02/2021	22:00:00	0
1	01/02/2021	23:00:00	0

Thanks in advance!

Re: Generate missing data and flag data

Saravanan_Desingh — Fri, 12 Feb 2021 00:56:54 GMT

One solution is,

tab1: LOAD * INLINE [ UserID, Type, EventDateTime 1, 1, 2021-02-01 10:00:00 1, 2, 2021-02-01 15:20:00 ]; Left Join(tab1) LOAD UserID,Only(Date(Date#(EventDateTime,'YYYY-MM-DD hh:mm:ss'),'hh')) As StHr Resident tab1 Where Type=1 Group By UserID; Left Join(tab1) LOAD UserID,Only(Date(Date#(EventDateTime,'YYYY-MM-DD hh:mm:ss'),'hh')) As EdHr Resident tab1 Where Type=2 Group By UserID; tab2: NoConcatenate LOAD *, If(Hour(Hr)>=Hour(StHr) And Hour(Hr)<=Hour(EdHr),1,0) As Flag; LOAD *,Time(IterNo()/24) As Time,Time(IterNo()/24,'hh') As Hr While IterNo()<=24; LOAD DISTINCT UserID, Date(Floor(Date#(EventDateTime,'YYYY-MM-DD hh:mm:ss'))) As Date, StHr, EdHr Resident tab1; Drop Table tab1;

Re: Generate missing data and flag data

Saravanan_Desingh — Fri, 12 Feb 2021 00:57:24 GMT

Output:

Re: Generate missing data and flag data

andrefpc — Mon, 22 Mar 2021 16:15:16 GMT

Had to change it a little but definitely did the trick! Thank you!