Qlik Community

App Development

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

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
andrefpc
Partner
Partner

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;

View solution in original post

Saravanan_Desingh

Output:

commQV88.PNG

andrefpc
Partner
Partner
Author

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