How to convert Rows to columns

[prerakdt]

Hi,

I have below data.

Id	Bucket
111	India
111	USA
111	UK
222	Japan
222	India
222	France
333	Spain
333	Australia

I want to convert it to below format, how can I do that?

Id	India	USA	UK	Japan	France	Spain	Australia
111	1	1	1	0	0	0	0
222	1	0	0	1	1	0	0
333	0	0	0	0	0	1	1

[avkeep01]

Hi @prerakdt 

You'll need a generic load. 

GENERIC LOAD Id AS Key, Bucket, 1 AS Value INLINE [
Id, Bucket
111, India
111, USA
111, UK
222, Japan
222, India
222, France
333, Spain
333, Australia
];

The only thing with Generic loads is that it creates a lot of tables. In this case it will be fine, but with more countries it will be a messy data model. The data will be correct, but it will return a lot of tables in your data model. 

Result: 

Key	India	USA	UK	Japan	France	Spain	Australia
111	1	1	1
222	1			1	1
333						1	1

[IamAlbinAntony]

Try this reverse cross table script,

[Data]:
LOAD
Id,
Bucket
FROM [lib://Sample/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left join (Data)

LOAD *, count(Bucket) as value RESIDENT [Data]
group by Id,Bucket;

[TMP1]:

GENERIC LOAD * RESIDENT [Data];

[RESULT]:

LOAD DISTINCT Id RESIDENT [Data];

DROP TABLE [Data];

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'TMP1.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

DROP Table TableList;

[prerakdt]

@IamAlbinAntony 

I am seeing null in place of 0. Is it possible to put 0 wherever it is null? 

[ArnadoSandoval]

Hi @prerakdt 

Please take a look at these two topics, they explain the GENERIC LOAD with example to put all the data in a single table, and deleting all the temporary tables:

Can you make a Straight Table horizontal in Qlik Sense  (my reply explaining the Generic Load)

The Generic Load  (2014 explanation of the Generic Load by Henric_Cronström)

Hope this helps,

[avkeep01]

Hi @prerakdt , 

I've made a script for you to add the 0 rows. I made all possible combinations and took the max of it (1 if existing 0 when not existing). Then I did the generic reload. 

Script: 

temp01_Table: 
LOAD Id, Bucket, 1 AS Value 
INLINE [
Id, Bucket
111, India
111, USA
111, UK
222, Japan
222, India
222, France
333, Spain
333, Australia
];

// Get all unique ID's 
temp01_CrossJoin:
LOAD DISTINCT Id RESIDENT temp01_Table;

// Get all unique buckets
temp01_Bucket:
NOCONCATENATE LOAD DISTINCT Bucket RESIDENT temp01_Table; 

	// Make all possible bucket ID combinations
	LEFT JOIN (temp01_CrossJoin) LOAD
		Bucket 
	RESIDENT temp01_Bucket;
	DROP TABLE temp01_Bucket; 

	// Assign 0 to alle possible combinations and add to the existing combinations
	temp02_CrossJoin: 
	CONCATENATE (temp01_Table) LOAD 
		Id, 
		Bucket, 
		0 AS Value
	RESIDENT temp01_CrossJoin;
	DROP TABLE temp01_CrossJoin; 

temp02_Table: 
NOCONCATENATE LOAD 
	Id, 
	Bucket, 
	MAX(Value) AS Value  // Highest value = 1 based on given data otherwise 0 based on created data  
RESIDENT temp01_Table
GROUP BY Id, Bucket; 
DROP TABLE temp01_Table; 

// Unpivot data 
Data: 
GENERIC LOAD 
Id AS Key, 
Bucket, 
Value 
RESIDENT temp02_Table; 
DROP TABLE temp02_Table 

Result: 

Key	India	USA	UK	Japan	France	Spain	Australia
333	0	0	0	0	0	1	1
222	1	0	0	1	1	0	0
111	1	1	1	0	0	0	0