Opposite of Cross table

[Qlikuser225]

Hi All,

Can you please help me with below 

I have data like 

Load * inline [

ID , Qtr, Category, Count, Value

1, Q1, AAA, 50, 6

1, Q1, BBB, 50, 6

1, Q1, CCC, 50, 0 

2, Q1, AAA, 50, 7

2, Q1, BBB, 50, 7

2, Q1, CCC, 50, 0

3, Q1, AAA, 50, 0

3, Q1, BBB, 50, 0

3, Q1, CCC, 50, 6];

i need data like below

ID , Qtr, AAA,  BBB,  CCC 

1 , Q1 ,   50 , 50 , 50

2 , Q1 ,   50 , 50 , 50

Thanks in advance

3 , Q1 ,   50 , 50 , 50

[vinieme12]

use can either use generic load , refer below url

https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470

OR simply Aggregate as below

temp:

Load 

ID , Qtr

, if(Category='AAA',Count) as AAA_Count

, if(Category='BBB',Count) as BBB_Count

, if(Category='CCC',Count) as CCC_Count

, if(Category='AAA',Value) as AAA_Value

, if(Category='BBB',Value) as BBB_Value

, if(Category='CCC',Value) as CCC_Value

inline [

ID , Qtr, Category, Count, Value

1, Q1, AAA, 50, 6

1, Q1, BBB, 50, 6

1, Q1, CCC, 50, 0 

2, Q1, AAA, 50, 7

2, Q1, BBB, 50, 7

2, Q1, CCC, 50, 0

3, Q1, AAA, 50, 0

3, Q1, BBB, 50, 0

3, Q1, CCC, 50, 6];

NOCONCATENATE

Final:

Load 

ID,Qtr

,sum(AAA_Count) as AAA_Count
,sum(BBB_Count) as BBB_Count

,sum(CCC_Count) as CCC_Count

,sum(AAA_Value) as AAA_Value

,sum(BBB_Value) as BBB_Value

,sum(CCC_Value) as CCC_Value

Resident temp

Group by ID,Qtr;

Drop table temp;

[LRuCelver · Accepted solution]

Here is an example of how you could use Generic to load the data:

Data:
NoConcatenate Load
	ID & '|' & Qtr as GenericID,
    Category,
    Count & '|' & Value as GenericVals
Inline [
    ID, Qtr, Category, Count, Value
    1, Q1, AAA, 50, 6
    1, Q1, BBB, 50, 6
    1, Q1, CCC, 50, 0 
    2, Q1, AAA, 50, 7
    2, Q1, BBB, 50, 7
    2, Q1, CCC, 50, 0
    3, Q1, AAA, 50, 0
    3, Q1, BBB, 50, 0
    3, Q1, CCC, 50, 6
];


GenericTable:
Generic Load 
	GenericID,
    Category,
    GenericVals
Resident Data;

Categories: NoConcatenate Load Distinct Category Resident Data;
Table: NoConcatenate Load Null() as ID AutoGenerate 0;

For vIndex = 0 to NoOfRows('Categories') - 1
	Let vCategory = Peek('Category', $(vIndex), 'Categories');

	Join(Table) Load
    	SubField(GenericID, '|', 1) as ID,
    	SubField(GenericID, '|', 2) as Qtr,
        SubField("$(vCategory)", '|', 1) as "$(vCategory)_Count",
        SubField("$(vCategory)", '|', 2) as "$(vCategory)_Value"
    Resident "GenericTable.$(vCategory)";
    
    Drop Table "GenericTable.$(vCategory)";

Next vIndex
Let vIndex;
Let vCategory;
Drop Tables Data, Categories;