Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
maprinci
Contributor II
Contributor II

Need to create calendar or new table where Value field changes based on Modified Date

I have a dataset that I need to use to derive a calendar or expanded table for every day of the year for multiple calendar years.

An example data table is shown below.

IDValueModified Date
141/1/2020
165/27/2020
2812/1/2019
2121/1/2021

 

For ID = 1, I need the 'Value' field to be 4 for everyday before 1/1/2020 until the 'Modified Date' of 5/27/2020, when the Value changes to 6.  Then, I need the Value to be 6 starting on 5/27/2020 and every day after that.  How would I create a calendar or new table for multiple different IDs with different Values and different Modified Dates?

 

Labels (2)
3 Replies
MayilVahanan

Hi @maprinci 

may be try like below

Temp:
LOAD *,RowNo() as RowNo INLINE [
ID, Value, Modified Date
1, 4, 1/1/2020
1, 6, 5/27/2020
2, 8, 12/1/2019
2, 12, 1/1/2021
];

Temp1:
Load *, If(ID = Previous(ID), If(Isnull(Peek([Modified Date])), [Modified Date],Peek([Modified Date])), Date(Today())) as EndDate Resident Temp order by RowNo desc;

Final:
Load *, Date([Modified Date] + IterNo() - 1) as Date Resident Temp1 While [Modified Date] + IterNo() - 1 <= EndDate order by RowNo;

DROP Table Temp, Temp1;

 

Use "Date " field in Front end. Hope it helps

Thanks & Regards,
Mayil Vahanan R
maprinci
Contributor II
Contributor II
Author

Thank you @MayilVahanan. Do you know of an easy way to update the code so that the "Date" only shows up once for when the "Value" updates? Right now, it shows up twice. In the example below, I would only want 5/27 to have the value of 6. 

maprinci_0-1620248405054.png

 

MayilVahanan

HI @maprinci 

Remove the "=" sign in the while condition, hope it helps

Load *, Date([Modified Date] + IterNo() - 1) as Date Resident Temp1 While [Modified Date] + IterNo() - 1 < EndDate order by RowNo;

Thanks & Regards,
Mayil Vahanan R