add next 4 years to existing year

[BI_Dev]

Hi - I have table as below.

ID	End Date	2021	2022	2023	2024	2025
1	12/31/2021	100
2	12/31/2023	100	200

Here ID '1' has the End date of 12/31/2021 - so, we want to assume it has the end date of  2025 and add $100 to 2022, 2023, 2024,2025

and ID '2' has the end date of  2023 - so want to assume it has the end date of 2025 and add $150 (pick the last available amount) to 2024 and 2025.

The output will be as below.

ID	End Date	2021	2022	2023	2024	2025
1	12/31/2021	100	100	100	100	100
2	12/31/2023	100	200	150	150	150

Is this doable ?

[stevejoyce]

Where do you equate " so want to assume it has the end date of 2025 and add $150 (pick the last available amount" = 150 based on your sample data set?

Is it the average or years where there is data?

[BI_Dev]

Hi - @stevejoyce its years where there is data.

[stevejoyce]

Sorry that question wasn't clear.

based on your data set, how did you get $150 for "pick the last available amount".  I don't see $150 in the raw data set at all.

[BI_Dev]

@stevejoyce OHHH..I am so sorry, my bad.Its $100 for ID 1 and 200 for ID 2.

O/P would be as below.

ID	End Date	2021	2022	2023	2024	2025
1	12/31/2021	100	100	100	100	100
2	12/31/2023	100	200	200	200	200

[stevejoyce]

I would unpivot the table and then you can use peek() function.  If you need to pivot back you can use generic load.  See below for the unpivot and filling in of data.

tempTable1:
crosstable (Year, Data_raw, 2)
LOAD
*
from https://community.qlik.com/t5/New-to-Qlik-Sense/add-next-4-years-to-existing-year/m-p/1833605#M19075...

(html, utf8, embedded labels, table is @1)
;

data:
noconcatenate
load *
,if(ID<>peek(ID), Data_raw, if(len(Data_raw)=0, peek(Data), Data_raw)) as Data
resident tempTable1
order by ID asc, [End Date] asc, Year asc ;

drop table tempTable1;
drop field Data_raw;

[BI_Dev]

@stevejoyce  - Thank you, but this solution is dropping all the years without data. PFA files which i tested.

[BI_Dev]

@Kushal_Chawda @sunny_talwar  any thoughts  please ?

[Kushal_Chawda]

@BI_Dev  next 4 years of data will be available in your data set or need to be calculated?

[stevejoyce · Accepted solution]

I guess crosstable doesn't like the nulls.  Please see below.  I am converting nulls to blank first, then continuing, and setting back to NullAsNull at the end.  I tested this and it works as expected.

NullAsValue * ;

Set NullValue = '';

//intermediate load data setting nulls to blank
tempSourceData:
LOAD *
FROM
[C:\Users\sjoyce\Downloads\Test (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

//cross table load temp data
tempTable1:
crosstable (Year, Data_raw, 2)
LOAD
*
resident tempSourceData;

drop table tempSourceData;

//logic for Data
data:
noconcatenate
load *
,if(ID<>peek(ID), Data_raw, if(len(Data_raw)=0, peek(Data), Data_raw)) as Data
resident tempTable1
order by ID asc, [End Date] asc, Year asc ;

drop table tempTable1;
drop field Data_raw;

//reset NullAsValue back to NullAsNull
NullAsNull * ;

exit script;