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: 
SK28
Creator
Creator

Transpose the table which has 1(single row ) and more than 100 colums

Hi,

 

I have a table that has a single row of values and more than 100 columns

 

Here is the example

 Table in this format wit 1 row and morethan 100 columns  
       
__KEY_fields__KEY_issuesaggregatetimespentCostcreatedcustomfield_10000customfield_10001
Not_Null_fieldsNot_Null_fieldsNot_Null_fieldsNot_Null_fieldsNot_Null_fieldsNull_fieldNull_field

 

 

and expected output is 

 

columnsNull_flag
__KEY_fieldsNot_Null_fields
__KEY_issuesNot_Null_fields
aggregatetimespentNot_Null_fields
CostNot_Null_fields
createdNot_Null_fields
customfield_10000Null_field
customfield_10001Null_field

 

 

I need a Data load editor script for this, please?

Thanks in advance

Labels (2)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

Try this:

 

Transform:
Crosstable(Columns, Flag)
LOAD
1 as Field1,
*
FROM [lib://Data/Convert to Straight table script in data load editor.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Drop Field Field1;

 

You need to have one field to keep before Crosstable function. This can be a dummy field that you drop after the transformation.

View solution in original post

2 Replies
Lisa_P
Employee
Employee

Try this:

 

Transform:
Crosstable(Columns, Flag)
LOAD
1 as Field1,
*
FROM [lib://Data/Convert to Straight table script in data load editor.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Drop Field Field1;

 

You need to have one field to keep before Crosstable function. This can be a dummy field that you drop after the transformation.

SK28
Creator
Creator
Author

Thank you, Lisa, It's working now.

Field1 has resolved the issue, Thank you so much