Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner
Partner

Generated Min and Second Min Dates based on other values

Hi all,

I've my source data in the below format

IDExit CodeEntry CodeDate
A123COCO01.09.2020
A123COCO03.09.2020
A123COCO05.09.2020
A123CO 15.09.2020
A123 CO23.11.2020
A123COCO26.11.2020
A123CO 05.12.2020
A123 CO06.12.2020
A123COCO07.12.2020
A123COCO08.12.2020

 

I'm looking to generate 4 Dates for the ID -

Entry - The first Date

Exit - The first Date where the Entry Code is empty

Re-Entry - The first Date after the Date where Exit Code is empty

Re-Exit - The Second Date where the Entry Code is empty

my expected output would be as below

IDEntryExitRe-EntryRe-Exit
A12301.09.202015.09.202023.11.202005.12.2020

 

Any pointers here would be really helpful.

 

Regards

Sai

 

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

Hi @Sai33 

Try like below

One of method to achieve the result.

Temp:
Load *, if(Len(Trim([Exit Code]))=0 and ex = 1, Date2) as Re_Entry, if(Len(Trim([Entry Code]))=0 and et = 2, Date2) as Re_Exit, if(Len(Trim([Entry Code]))=0 and et = 1, Date2) as Exit;
LOAD *, if(ID = Peek(ID), if(Len(Trim([Exit Code]))=0, Peek('ex')+1, alt(Peek('ex'),0)), 0) as ex,
if(ID = Peek(ID), if(Len(Trim([Entry Code]))=0, Peek('et')+1, alt(Peek('et'),0)),0) as et INLINE [
ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

Load ID, Date(Min(Date2)) as Entry, Date(Min(Exit)) as Exit, Date(Min(Re_Entry)) as Re_Entry, Date(Min(Re_Exit)) as Re_Exit Resident Temp
group by ID;

MayilVahanan_0-1607681670831.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

9 Replies
edwin
Master
Master

look at attached if this is what you need.  it determines the min date per ID, and then to get the next, the same formula becomes a criteria where dates are greater than that

Kushal_Chawda

@Sai33  do you want to do it in script or front end?

Sai33
Partner
Partner
Author

Hi, @Kushal_Chawda  in script

Sai33
Partner
Partner
Author

Hi all,

Any quick suggestions here, looking to build this logic in Script.

Thanks.

Barttonn
Contributor
Contributor

The Excel MIN function returns the smallest numeric value in a range of values. This generates an array of TRUE / FALSE values, where TRUE corresponds to rows the MIN and TODAY functions to find the "next date" based on the date today. The formulas shown in the example all use the AVERAGE function with a .

MayilVahanan

Hi @Sai33 

Try like below

One of method to achieve the result.

Temp:
Load *, if(Len(Trim([Exit Code]))=0 and ex = 1, Date2) as Re_Entry, if(Len(Trim([Entry Code]))=0 and et = 2, Date2) as Re_Exit, if(Len(Trim([Entry Code]))=0 and et = 1, Date2) as Exit;
LOAD *, if(ID = Peek(ID), if(Len(Trim([Exit Code]))=0, Peek('ex')+1, alt(Peek('ex'),0)), 0) as ex,
if(ID = Peek(ID), if(Len(Trim([Entry Code]))=0, Peek('et')+1, alt(Peek('et'),0)),0) as et INLINE [
ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

Load ID, Date(Min(Date2)) as Entry, Date(Min(Exit)) as Exit, Date(Min(Re_Entry)) as Re_Entry, Date(Min(Re_Exit)) as Re_Exit Resident Temp
group by ID;

MayilVahanan_0-1607681670831.png

 

Thanks & Regards,
Mayil Vahanan R
Sai33
Partner
Partner
Author

Hi @MayilVahanan 

Your code also works perfectly, but i've found a issue in my data

ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020

A123,CO,,15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020

A123,Co,,05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];

 

For each empty Entry Code- i've two entreis in my DB. This is leading the Exit and Re-Exit dates to always be the same. 

I've updated my table in the comment here. Can you please help me out here.

 

Thanks

Sai

MayilVahanan

Hi @Sai33 

Try like below

Temp:

LOAD Distinct * INLINE [
ID, Exit Code, Entry Code, Date2
A123, CO, CO, 01.09.2020
A123, CO, CO, 03.09.2020
A123, CO, CO, 05.09.2020
A123, CO, , 15.09.2020
A123, CO, , 15.09.2020
A123, , CO, 23.11.2020
A123, CO, CO, 26.11.2020
A123, CO, , 05.12.2020
A123, , CO, 06.12.2020
A123, CO, CO, 07.12.2020
A123, CO, CO, 08.12.2020
];


Temp1:
Load *, if(Len(Trim([Exit Code]))=0 and ex = 1, Date2) as Re_Entry, if(Len(Trim([Entry Code]))=0 and et = 2, Date2) as Re_Exit, if(Len(Trim([Entry Code]))=0 and et = 1, Date2) as Exit;
Load *, if(ID = Peek(ID), if(Len(Trim([Exit Code]))=0, Peek('ex')+1, alt(Peek('ex'),0)), 0) as ex,
if(ID = Peek(ID), if(Len(Trim([Entry Code]))=0, Peek('et')+1, alt(Peek('et'),0)),0) as et resident Temp;


Load ID, Date(Min(Date2)) as Entry, Date(Min(Exit)) as Exit, Date(Min(Re_Entry)) as Re_Entry, Date(Min(Re_Exit)) as Re_Exit Resident Temp1
group by ID;

DROP Table Temp, Temp1;

Thanks & Regards,
Mayil Vahanan R