How to transfer crosstable

[Jeff2022]

I have met question in my work like this

how to transfer table by following steps:

Step1: table1 in to table2, and add three columns (2021_fruit_total/2020_fruit_total/Growth(2021/2020))

1,table1

CustomerName	Purchase_date	Category_type	volume
A	2021-02	organge	100
B	2021-10	apple	200
C	2021-09	bear	300
D	2021-02	lemon	301
E	2021-09	organge	302
F	2021-02	apple	303
A	2020-02	organge	101
B	2020-10	apple	201
C	2020-09	bear	301
D	2020-02	lemon	302
E	2020-09	organge	303
F	2020-02	apple	304

2, Table2

CustomerName	Purchase_date	organge	apple	bear	lemon	2021_fruit_total	2020_fruit_total	Growth(2021/2020)
A	2021-02	100				100		-0.99%
B	2021-10		200			200		-0.50%
C	2021-09			300		300		-0.33%
D	2021-02				301	301		-0.33%
E	2021-09	302				302		-0.33%
F	2021-02		303			303		-0.33%
A	2020-02	101					101
B	2020-10		201				201
C	2020-09			301			301
D	2020-02				302		302
E	2020-09	303					303
F	2020-02		304				304

Step2, transfer Table2 into Table3

Table3

CustomerName	Purchase_date	Category_type	Value
A	2021-02	organge	100
B	2021-10	apple	200
C	2021-09	bear	300
D	2021-02	lemon	301
E	2021-09	organge	302
F	2021-02	apple	303
A	2020-02	organge	101
B	2020-10	apple	201
C	2020-09	bear	301
D	2020-02	lemon	302
E	2020-09	organge	303
F	2020-02	apple	304
A	2021-02	2021_fruit_total	100
B	2021-10	2021_fruit_total	200
C	2021-09	2021_fruit_total	300
D	2021-02	2021_fruit_total	301
E	2021-09	2021_fruit_total	302
F	2021-02	2021_fruit_total	303
A	2021-02	2020_fruit_total	101
B	2021-10	2020_fruit_total	201
C	2021-09	2020_fruit_total	301
D	2021-02	2020_fruit_total	302
E	2021-09	2020_fruit_total	303
F	2021-02	2020_fruit_total	304
A	2021-02	Growth(2021/2020)	-0.99%
B	2021-10	Growth(2021/2020)	-0.50%
C	2021-09	Growth(2021/2020)	-0.33%
D	2021-02	Growth(2021/2020)	-0.33%
E	2021-09	Growth(2021/2020)	-0.33%
F	2021-02	Growth(2021/2020)	-0.33%

it's so difficult for me, could you pls help me? Thank you so so much!

[Joseph_Musekura · Accepted solution]

Hi @Jeff2022 ,

I think in your case you need to use the concepts of generic table https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/DataSource/generic-d...

A  crossTable  prefix is used  to turn a cross table into a straight table, that is, a wide table with many columns  is turned into a tall table https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref... 

Regards,