How to pivot this table?

[reporting_neu]

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?

[Chanty4u]

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

];

[reporting_neu]

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;

I get the error message:
"Illegal combination of prefixes"

[reporting_neu]

Can someone help me, please?

[Kushal_Chawda]

@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.

[reporting_neu]

I then have to do a calculation (peek) to add the different lines together, which unfortunately isn't possible otherwise.

[menta]

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;

[AustinSpivey]

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:

• Uses the AutoNumber() function to create a unique key for each distinct combination of [Account] and [Year]. We name this new field [RowID].
• We then use the Generic prefix keyword to Resident Load our [TMP] table. This will generate separate tables for each [Art] value, where each generated table has records with [RowId] and the values from the [Amount] field.
• We create a new empty table called [New Table] that we will join all of the generated tables into.
• Now we loop through all of the tables in our data model and if any of them were generated by our Generic load (they'll start with the generic table name we used, 'generic_TMP' in this case) then we join them into our [New Table].
• We should now have the results we want:

And if you want those Nulls to be shown as zeros instead, just use the Alt() function or Coalesce() function to do that.