Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
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?
Hi - @stevejoyce its years where there is data.
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.
@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 |
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;
@stevejoyce - Thank you, but this solution is dropping all the years without data. PFA files which i tested.
@Kushal_Chawda @sunny_talwar any thoughts please ?
@BI_Dev next 4 years of data will be available in your data set or need to be calculated?
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;