Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
andrefpc
Partner - Creator II
Partner - Creator II

Generate missing data and flag data

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:

UserIDTypeEventDateTime
112021-02-01 10:00:00
122021-02-01 15:20:00

 

Desired Output:

UserIDDateTimeFlag
101/02/202100:00:000
101/02/202101:00:000
101/02/202102:00:000
101/02/202103:00:000
101/02/202104:00:000
101/02/202105:00:000
101/02/202106:00:000
101/02/202107:00:000
101/02/202108:00:000
101/02/202109:00:000
101/02/202110:00:001
101/02/202111:00:001
101/02/202112:00:001
101/02/202113:00:001
101/02/202114:00:001
101/02/202115:00:001
101/02/202116:00:000
101/02/202117:00:000
101/02/202118:00:000
101/02/202119:00:000
101/02/202120:00:000
101/02/202121:00:000
101/02/202122:00:000
101/02/202123:00:000

 

 

Thanks in advance!

1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

3 Replies
Saravanan_Desingh

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:

commQV88.PNG

andrefpc
Partner - Creator II
Partner - Creator II
Author

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