Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
samvile18
Creator III
Creator III

Can anyone help please...

Morning all,

I have the following table in Qlik that I need to add two more columns to. I'm struggling a bit with the syntax, either in the script or within the expression of a table. 

QlikTable.JPG

I need one line per scheme that shows;

SchemeNo - Max(CompletedDate) - Next Due - Next Due+1

F47478                10/08/2020                  01/12/2020  01/03/2021

Can anyone suggest how I can achieve this please? 

Help is much appreciated.

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@samvile18  One solution :

Input table :

Capture.PNG

Script:

Input:

load * inline [
Sheme Number,DueDate,CompletedDate
F47478,01/02/2019,01/02/2019
F47478,01/04/2019,04/02/2019
F47478,01/05/2019,05/02/2019
F47478,07/02/2019,10/08/2020
F47478,01/12/2020,
F47478,01/03/2021,
F47478,07/03/2021,
F47478,07/03/2022,
F47478,07/07/2022,
F47478,07/05/2022,
F47478,07/04/2022,
];

Tmp:
noconcatenate
load [Sheme Number],Date(Max(CompletedDate,1)) as [Max CompletedDate] resident Input where len(trim(CompletedDate))>0 group by [Sheme Number];
left join
load [Sheme Number],Date(Min(DueDate,1)) as [Next Due],Date(Min(DueDate,2)) as [Next Due+1] resident Input where len(trim(CompletedDate))=0 group by [Sheme Number];


drop table Input;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra
Master II
Master II

@samvile18  One solution :

Input table :

Capture.PNG

Script:

Input:

load * inline [
Sheme Number,DueDate,CompletedDate
F47478,01/02/2019,01/02/2019
F47478,01/04/2019,04/02/2019
F47478,01/05/2019,05/02/2019
F47478,07/02/2019,10/08/2020
F47478,01/12/2020,
F47478,01/03/2021,
F47478,07/03/2021,
F47478,07/03/2022,
F47478,07/07/2022,
F47478,07/05/2022,
F47478,07/04/2022,
];

Tmp:
noconcatenate
load [Sheme Number],Date(Max(CompletedDate,1)) as [Max CompletedDate] resident Input where len(trim(CompletedDate))>0 group by [Sheme Number];
left join
load [Sheme Number],Date(Min(DueDate,1)) as [Next Due],Date(Min(DueDate,2)) as [Next Due+1] resident Input where len(trim(CompletedDate))=0 group by [Sheme Number];


drop table Input;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

samvile18
Creator III
Creator III
Author

Yep - outstanding, thanks very much for your time.