Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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!