Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a data source that looks like the following:
Test:
Load
*
Inline
[
Account, Year, Amount, Art
32542, 2022, 1254, ZE
32542, 2021, 37, ZE
32542, 2020, 4587, ZE
32542, 2019, 4689, ZE
32542, 2022, 856, ERE
32542, 2021, 7864, ERE
32542, 2020, 1315, ERE
32542, 2018, 8946, ERE
12345, 2023, 500, ZE
12345, 2024, 500, ERE
]
;
Within the load editor, I want to pivot this table on Account and Year, turning Amount values into column headers "ZE" and "ERE". So, I want my output to look like:
Account Year ZE ERE
32542 2022 1254 856
32542 2021 37 7864
32542 2020 4587 1315
32542 2019 4689 0
32542 2018 0 8946
12345 2023 500 0
12345 2023 0 500
How can I do it?
Hi
It can be possible through cross load
Test:
Crosstable(Art, Amount, 2)
Load Account, Year, Amount
Inline
[
Account, Year, Amount, Art
32542, 2022, 1254, ZE
32542, 2021, 37, ZE
32542, 2020, 4587, ZE
32542, 2019, 4689, ZE
32542, 2022, 856, ERE
32542, 2021, 7864, ERE
32542, 2020, 1315, ERE
32542, 2018, 8946, ERE
12345, 2023, 500, ZE
12345, 20
24, 500, ERE
];
Many thanks for your response! 😄
How can I use the code if I refer to a pre-existing table and want to use "resident"?
If I use:
NoConcatenate
TMP1:
Crosstable(Art, Amount, 2)
Load Account, Year, Amount Resident TMP;
Can someone help me, please?
@reporting_neu Why do you want to pivot this table into script? Your original data source is in proper table structure required for Qlik to analyze data where you can use Art as dimension. Instead I would suggest to create pivot table on front end and then represent Art us column dimension to represent in format you want.
I then have to do a calculation (peek) to add the different lines together, which unfortunately isn't possible otherwise.
You can load twice the table with a condition
Tmp:
Load
*
Inline
[
Account, Year, Amount, Art
32542, 2022, 1254, ZE
32542, 2021, 37, ZE
32542, 2020, 4587, ZE
32542, 2019, 4689, ZE
32542, 2022, 856, ERE
32542, 2021, 7864, ERE
32542, 2020, 1315, ERE
32542, 2018, 8946, ERE
12345, 2023, 500, ZE
12345, 2024, 500, ERE
]
;
Table:
LOAD
Account,
Year,
Amount as ZE
resident TMP
WHERE Art='ZE';
join (Table)
LOAD
Account,
Year,
Amount as ERE
resident TMP
WHERE Art='ERE';
drop table Tmp;
You should use the Generic keyword to achieve this, as that will allow you to "un-pivot" the way you are intending to. I wrote a step-by-step guide in this post about how to do this (start at step 3).
This is this script you could use:
[TMP]:
Load *
, AutoNumber([Account] & '|' & [Year]) as [RowID]
Inline [
Account , Year , Amount , Art
32542 , 2022 , 1254 , ZE
32542 , 2021 , 37 , ZE
32542 , 2020 , 4587 , ZE
32542 , 2019 , 4689 , ZE
32542 , 2022 , 856 , ERE
32542 , 2021 , 7864 , ERE
32542 , 2020 , 1315 , ERE
32542 , 2018 , 8946 , ERE
12345 , 2023 , 500 , ZE
12345 , 2024 , 500 , ERE
];
[generic_TMP]:
Generic Load
[RowID]
, [Art]
, [Amount]
Resident [TMP];
[New Table]: Load * Inline [RowID];
For tbl = NoOfTables() to 0 Step -1
Let vCurrentTable = TableName($(tbl));
If '$(vCurrentTable)' Like 'generic_TMP.*' Then
Join ([New Table]) Load * Resident [$(vCurrentTable)];
Drop Table [$(vCurrentTable)];
End If
Next tbl
That script does the following:
And if you want those Nulls to be shown as zeros instead, just use the Alt() function or Coalesce() function to do that.