Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th 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.