topic Re: Generated Min and Second Min Dates based on other values in QlikView

Generated Min and Second Min Dates based on other values

Sai33 — Sat, 16 Nov 2024 17:43:13 GMT

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

Re: Generated Min and Second Min Dates based on other values

edwin — Thu, 10 Dec 2020 19:48:44 GMT

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

Re: Generated Min and Second Min Dates based on other values

Kushal_Chawda — Thu, 10 Dec 2020 22:30:58 GMT

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

Re: Generated Min and Second Min Dates based on other values

Sai33 — Fri, 11 Dec 2020 07:30:00 GMT

Hi, @Kushal_Chawda in script

Re: Generated Min and Second Min Dates based on other values

Sai33 — Fri, 11 Dec 2020 09:37:09 GMT

Hi all,

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

Thanks.

Re: Generated Min and Second Min Dates based on other values

Barttonn — Fri, 11 Dec 2020 09:48:19 GMT

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 .

Re: Generated Min and Second Min Dates based on other values

MayilVahanan — Fri, 11 Dec 2020 10:14:55 GMT

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;

Re: Generated Min and Second Min Dates based on other values

Sai33 — Fri, 11 Dec 2020 12:47:31 GMT

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

Re: Generated Min and Second Min Dates based on other values

MayilVahanan — Fri, 11 Dec 2020 16:24:03 GMT

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;

Re: Generated Min and Second Min Dates based on other values

blacksmith15590 — Mon, 14 Feb 2022 16:40:52 GMT

walmartone wire

myaccountaccess.space

One Vanilla gifts

OneCognizant login