Can anyone help please...

[samvile18]

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.

[Taoufiq_Zarra · Accepted solution]

@samvile18  One solution :

Input table :

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:

[samvile18]

Yep - outstanding, thanks very much for your time.