Generate missing data and flag data

[andrefpc]

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!

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Output:

[andrefpc]

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