topic Re: Can anyone help please... in QlikView

Can anyone help please...

samvile18 — Thu, 08 Oct 2020 09:28:46 GMT

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.

Re: Can anyone help please...

Taoufiq_Zarra — Thu, 08 Oct 2020 09:49:44 GMT

@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:

Re: Can anyone help please...

samvile18 — Thu, 08 Oct 2020 13:42:02 GMT

Yep - outstanding, thanks very much for your time.