Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Partner - Creator

## Generated Min and Second Min Dates based on other values

Hi all,

I've my source data in the below format

 ID Exit Code Entry Code Date 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

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

 ID Entry Exit Re-Entry Re-Exit A123 01.09.2020 15.09.2020 23.11.2020 05.12.2020

Any pointers here would be really helpful.

Regards

Sai

Labels (1)
• ### Min and max dates

1 Solution

Accepted Solutions
MVP

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
9 Replies
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

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

Partner - Creator
Author

Hi, @Kushal_Chawda  in script

Partner - Creator
Author

Hi all,

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

Thanks.

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 .

MVP

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Partner - Creator
Author

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

MVP

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
Please close the thread by marking correct answer & give likes if you like the post.
Contributor
Community Browser