topic Re: Looping dates in QlikView

Looping dates

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

Hope that you will help with this.

The Objective is the below output, I need to Create the Out put Date that appears in the below table:

Here is the formula that I need to create in Script : if(C='Allocated', Output Date = A (for the same Row), if(C='Attempt', we take the last date for Allocated )

As Shows below Row 3 in Output Date is '02/08/2017' as the last Allocated Date same for Row 4 since C='Attempt' Output Date take also '02/08/2017' With condition that B3=B2 and B4=B2 in other word same account Number.

"if all the above are not applicable we take the same date of Column A"

Thank you! Hope you can Help

A	B	C	Output Date
01/08/2017	00531546100200930	Allocated	01/08/2017
02/08/2017	00531546100200930	Allocated	02/08/2017
24/09/2017	00531546100200930	Attempt	02/08/2017
25/09/2017	00531546100200930	Attempt	02/08/2017
04/07/2017	00541440000200907	Allocated	04/07/2017
19/07/2017	00541440000200907	Allocated	19/07/2017
21/07/2017	00541440000200907	Attempt	19/07/2017
26/07/2017	00541440000200907	Attempt	19/07/2017

Re: Looping dates

settu_periasamy — Fri, 27 Jul 2018 08:04:10 GMT

Maybe try this.

T1:
LOAD A, B, C
FROM [https://community.qlik.com/thread/309193]
(html, codepage is 1252, embedded labels, table is @1) ;

NoConcatenate
T2:
LOAD A,B,C, if( C= 'Allocated',A,Peek('Output Date',RecNo()-2) )as [Output Date]
Resident T1 Order by B,A;

DROP Table T1;

Re: Looping dates

YoussefBelloum — Fri, 27 Jul 2018 08:11:12 GMT

Hi,

try this:

if(C='Allocated',A,if(C='Attempt',Peek(A))) as output

Re: Looping dates

mario-sarkis — Fri, 27 Jul 2018 08:30:30 GMT

Thank you Settu for your help, but I still have a small issue in below row in yellow should take the allocation date of the previous one since this was the last allocated Date for that account, for the previous rows all is good!!

how you can help !! Thank you so much

Account_Allocation	Allocation_Date_Final	Allocation_Flag	Final_Collector_Code	Output Date
531546100200930	8/1/2017	Allocated	COLL26	8/1/2017
531546100200930	8/2/2017	Allocated	COLL28	8/2/2017
531546100200930	9/24/2017	Allocated	COLL26	9/24/2017
541440000200907	7/4/2017	Allocated	COLL19	7/4/2017
541440000200907	7/19/2017	Attempt	COLL19	7/4/2017
541440000200907	7/21/2017	Attempt	COLL19	7/4/2017
541440000200907	7/26/2017	Allocated	COLL23	7/26/2017
541440000200907	7/26/2017	Attempt	COLL23	7/4/2017	7/26/2017

Re: Looping dates

qlikviewwizard — Fri, 27 Jul 2018 08:37:15 GMT

Hi,

Please check this.

Data_Temp:

LOAD Date#(A,'DD/MM/YYYY') as A,B,C INLINE [

A,B,C,Output Date

01/08/2017,00531546100200930,Allocated

02/08/2017,00531546100200930,Allocated

24/09/2017,00531546100200930,Attempt

25/09/2017,00531546100200930,Attempt

04/07/2017,00541440000200907,Allocated

19/07/2017,00541440000200907,Allocated

21/07/2017,00541440000200907,Attempt

26/07/2017,00541440000200907,Attempt

];

NoConcatenate

Data:

LOAD *, IF(C='Allocated',A,

IF(C='Attempt',Previous(A))) AS [Output Date];

LOAD * RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Re: Looping dates

mario-sarkis — Fri, 27 Jul 2018 08:45:14 GMT

Hi Arjun,

Thank you for your Response, but if you check Row 4 it took the value of 21/07/2017 this should be also 19/07/2018 since the allocated was on 19/07/2018

This mean that incase is attempt it should take the date of the last Allocated Date of the same account Number.

Thank you hope that you can help with this also

Thank you Again for your help!

Re: Looping dates

madhu_r — Fri, 27 Jul 2018 11:47:12 GMT

Hi,

Try this

Table1:

Load * Inline

[

INPUTDATE,B,C

01/08/2017,00531546100200930,Allocated

02/08/2017,00531546100200930,Allocated

24/09/2017,00531546100200930,Attempt

25/09/2017,00531546100200930,Attempt

04/07/2017,00541440000200907,Allocated

19/07/2017,00541440000200907,Allocated

21/07/2017,00541440000200907,Attempt

26/07/2017,00541440000200907,Attempt

];

NoConcatenate

load

If(C='Allocated',INPUTDATE,Peek(OUTPUTDATE) ) AS OUTPUTDATE

Resident Table1;

DROP Table Table1;

Re: Looping dates

Anonymous — Fri, 27 Jul 2018 13:30:59 GMT

Hi Mario,

Please use,

309193:
LOAD B AS Account_Allocation,
ALT(A,Date(Date#(A,'DD/MM/YYYY'),'DD/MM/YYYY'),Date(Date#(A,'D/M/YYYY'),'DD/MM/YYYY')) AS Allocation_Date_Final,
C AS Allocation_Flag ;

load * inline [
A, B, C
1/8/2017, 531546100200930, Allocated
2/8/2017, 531546100200930, Allocated
24/09/2017, 531546100200930, Attempt
25/09/2017, 531546100200930, Attempt
4/7/2017, 541440000200907, Allocated
19/07/2017, 541440000200907, Allocated
21/07/2017, 541440000200907, Attempt
26/07/2017, 541440000200907, Attempt

](delimiter is ',') ;

NoConcatenate
Final:
LOAD *,
if(Allocation_Flag='Allocated',Allocation_Date_Final,Peek(OutPutDate)) AS OutPutDate;
LOAD Account_Allocation,
Allocation_Date_Final,
Allocation_Flag
Resident 309193 order by Account_Allocation,Allocation_Date_Final;

DROP Table 309193;

Re: Looping dates

settu_periasamy — Fri, 27 Jul 2018 14:04:25 GMT

Hi Mario,

It may be issue of Order by. I have tried the same query, It gives the proper data. Can you check this

T1:
LOAD        Account_Allocation,
Allocation_Date_Final,
Allocation_Flag,
Final_Collector_Code
FROM [https://community.qlik.com/message/1522602#1522602]
(html, codepage is 1252, embedded labels, table is @2);

NoConcatenate
T2:
LOAD
Account_Allocation,
Allocation_Date_Final,
Allocation_Flag,
Final_Collector_Code,
if(Allocation_Flag='Allocated',Allocation_Date_Final,Peek('Output Date',RecNo()-2)) as [Output Date]
Resident T1 Order by Account_Allocation,   Allocation_Date_Final;

DROP Table T1;