Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator III

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.

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

@samvile18  One solution :

Input table :

Script:

``````Input:

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:

Regards,
Taoufiq ZARRA

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

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

@samvile18  One solution :

Input table :

Script:

``````Input:

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:

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Creator III
Author

Yep - outstanding, thanks very much for your time.

Community Browser