Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
nattapat
Contributor II
Contributor II

How to convert column to header and row

I just wondering is this possible to do in Qlik Data load editorcrosstab.PNG

I try to do the crosstab and generic load and still doesn't get the result that I want.

Labels (1)
2 Solutions

Accepted Solutions
marcus_sommer

If it are only a few dimension-values you may consider a more manual approach like:

t: load group, value as new from X where title = 'new';
join(t) load group, value as old from X where title = 'old';

but by an undefined number of values the mentioned generic load would be easier.

View solution in original post

Vegar
MVP
MVP

 

The script below creates the table you desire in the example above.

Vegar_0-1692776228634.png

 

GenericDB:
load * inline [
group, title, value
a,new,8
a,old,9
b,new,2
b,old,6
c,new,4
c,old,7
];
GenericLabel:
Generic load group, title, value resident GenericDB;

let vListOfTables = ;
For vTableNo = 0 to NoOfTables()
	Let vTableName = TableName($(vTableNo)) ;
    If Subfield(vTableName,'.',1)='GenericLabel' Then
		Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
    End If
Next vTableNo
   
CombinedGenericTable:
Load distinct group Resident GenericDB;

 
For each vTableName in $(vListOfTables)
  Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
  Drop Table [$(vTableName)];
Next vTableName

//Drop table GenericDB

 

 

View solution in original post

5 Replies
Vegar
MVP
MVP

I suggest you to take a second look at the generic load. Maybe this old post about the generic load written by HIC may help you forward.

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

marcus_sommer

If it are only a few dimension-values you may consider a more manual approach like:

t: load group, value as new from X where title = 'new';
join(t) load group, value as old from X where title = 'old';

but by an undefined number of values the mentioned generic load would be easier.

Vegar
MVP
MVP

 

The script below creates the table you desire in the example above.

Vegar_0-1692776228634.png

 

GenericDB:
load * inline [
group, title, value
a,new,8
a,old,9
b,new,2
b,old,6
c,new,4
c,old,7
];
GenericLabel:
Generic load group, title, value resident GenericDB;

let vListOfTables = ;
For vTableNo = 0 to NoOfTables()
	Let vTableName = TableName($(vTableNo)) ;
    If Subfield(vTableName,'.',1)='GenericLabel' Then
		Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
    End If
Next vTableNo
   
CombinedGenericTable:
Load distinct group Resident GenericDB;

 
For each vTableName in $(vListOfTables)
  Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
  Drop Table [$(vTableName)];
Next vTableName

//Drop table GenericDB

 

 

nattapat
Contributor II
Contributor II
Author

Thank you for your help. I try the script from https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470 and it's making me confuse. You help me understanding more with your script. Thank a lots.

nattapat
Contributor II
Contributor II
Author

Thank you for your help. Your script are works as well.