Create from & to date fields from date value

[kanonkop]

Good Day,

Hope everyone is well...

Hope someone can help me. I currently have a table with multiple items with a data associated with that record.

What I would like to do is create a table using that specific date and creating a table with a from and to date field.

For example see screenshot below... Item 1 has a date of 2019/04/08, the next date for Item 1 is 2019/04/20. Therefore in the new table a record should be created for Item 1, with from date 2019/04/08 and the to date field of 2019/04/20 -1. Then a new record where Item 1 from date is 2019/04/20, now because there is not another record for Item 1, it should then populate the to date field with 2099/12/31 for example.

Thanks and Regards,

Kanon

[vikasmahajan]

create 2 variables  vCYStartDate    1/4/2018  vCYEndDate  =date(today(),'DD/MM/YYYY')

use expression 

Sum({$<[Posting Date]={">=$(=vCYStartDate)<=$(=vCYEndDate)"}>} Sales)

Hope this helps you

Vikas

[kanonkop]

Thank you Vikas, I will attempt set analysis if I do not come right...

Is this possible though to do in the load script? Apologies I should have mentioned this.

[dplr-rn · Accepted solution]

You need to use previous function in your load script

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

load your data sorted on item and date desc

then some like below

load *
,YOURDATE as From-Date
, if(Item=Previous(Item)
      , Previous(YOURDATE)-1 //1 day before previous date
      ,'2099/12/31' //change this to actual date
) as To-Date

[kanonkop]

Thank you very much, your solution worked.

As reference for anyone else I used the below code for my example.

[temp]:
load * Inline
[Example, Item, Date,
Item, 1, 2019/04/08
Item, 2, 2019/04/09
Item, 3, 2019/04/10
Item, 1, 2019/04/20
Item, 3, 2019/04/14
Item, 2, 2019/04/22
Item, 3, 2019/04/18];

[Table]:
load *,
Date as From_Date,
if(Item=Previous(Item)
 ,Date(Previous(Date)-1,'YYYY/MM/DD')
    ,'2099/12/31'
) as To_Date
Resident [temp] Order by Item, Date desc;

drop Table [temp];