How to make cross table for transpose column

[Jeff2022]

Dears

I had met a question from transpose table1 to table in Qliksense script .

table1,

CustomerNo	Period	out_of_warranty	out_of_mc
A	2022-01	1	1
B	2022-01	1	2
C	2022-01	2	2
D	2022-01	1	1
A	2022-01	1	1
B	2022-01	1	1
C	2022-01	1	1
D	2022-01	1	0

Table2:

CustomerNo	Period	Catergory	Value
A	2022-01-01	out_of_warranty	10
B	2022-01-03	out_of_mc	20
C	2022-01-03	out_of_warranty	10
D	2022-01-07	out_of_mc	20
A	2022-01-08	out_of_warranty	10
B	2022-01-03	out_of_mc	20
C	2022-01-08	out_of_warranty	10
D	2022-01-03	out_of_mc	20

who can help? Thank you so much.

[Joseph_Musekura]

Hi @Jeff2022 

Did you check the concepts of "Generic table"  https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/DataSource/generic-d... ?

You have post similar question here https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-transfer-crosstable/td-p/1860304

/joseph

[abhijitnalekar]

Hi @Jeff2022 ,

Please try the below script.

main:
CrossTable(category,value,2)
LOAD
CustomerNo,
Period,
out_of_warranty,
out_of_mc
FROM [lib://test/Testing.xlsx]
(ooxml, embedded labels, table is Sheet6);

NoConcatenate

load
CustomerNo,
Period,
category,
sum(value)as value
Resident main
group By
CustomerNo,
Period,
category;

drop table main;