Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prerakdt
Contributor II
Contributor II

How to convert Rows to columns

Hi,

I have below data.

IdBucket
111India
111USA
111UK
222Japan
222India
222France
333Spain
333Australia

 

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

IdIndiaUSAUKJapanFranceSpainAustralia
1111110000
2221001100
3330000011

 

Labels (2)
5 Replies
avkeep01
Partner - Specialist
Partner - Specialist

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 
111111    
2221  11  
333     11
IamAlbinAntony
Creator
Creator

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
Contributor II
Contributor II
Author

@IamAlbinAntony 

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

ArnadoSandoval
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
avkeep01
Partner - Specialist
Partner - Specialist

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 
3330000011
2221001100
1111110000