Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Looping dates

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

ABCOutput Date
01/08/201700531546100200930Allocated01/08/2017
02/08/201700531546100200930Allocated02/08/2017
24/09/201700531546100200930Attempt02/08/2017
25/09/201700531546100200930Attempt02/08/2017
04/07/201700541440000200907Allocated04/07/2017
19/07/201700541440000200907Allocated19/07/2017
21/07/201700541440000200907Attempt19/07/2017
26/07/201700541440000200907Attempt19/07/2017
8 Replies
Highlighted

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;

Highlighted
Champion
Champion

Hi,

try this:


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

Highlighted
Creator II
Creator II

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

Highlighted
Master II
Master II

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;

Capture.PNG

Capture1.PNG

Highlighted
Creator II
Creator II

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!

Highlighted
Contributor II
Contributor II

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;Data.PNG

Highlighted
Contributor III
Contributor III

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;



Highlighted

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;

Capture.JPG