Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn 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!