Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
EliGohar
Partner - Creator III
Partner - Creator III

Row to column structure

Hi!

I have this table:

EliGohar_0-1707315519114.png

I want to transpose the table from row structure to columns, The final result should be:

objectid class_4 not_class_4 class_6 not_class_6 class8 not_class_8 class_9 not_class_9
2 1 1 0 1     0 0
3     0 1 0 0    

 

Can anyone advise how to achieve the desired output?
Thank you!

Labels (1)
1 Solution

Accepted Solutions
LRuCelver
Partner - Creator III
Partner - Creator III

I hate it, but it seems to work:

Data:
NoConcatenate Load * Inline [
	objectid,customclassid,positive,negative
    2,4,1,1
    2,6,0,1
    2,9,0,0
    3,8,0,0
    3,6,0,1
];

GenericTable:
Generic Load 
	objectid,
    customclassid,
    2 * positive + negative as state
Resident Data;

Classes: NoConcatenate Load Distinct customclassid Resident Data;
Table: NoConcatenate Load Null() as objectid AutoGenerate 0;

For vIndex = 0 to NoOfRows('Classes') - 1
	Let vClass = Peek('customclassid', $(vIndex), 'Classes');

	Join(Table) Load
    	objectid,
        Floor("$(vClass)" / 2) as "class_$(vClass)",
        Mod("$(vClass)", 2) as "not_class_$(vClass)"
    Resident "GenericTable.$(vClass)";
    
    Drop Table "GenericTable.$(vClass)";

Next vIndex
Let vIndex;
Let vClass;
Drop Tables Data, Classes;

View solution in original post

4 Replies
Anil_Babu_Samineni

@EliGohar please share the data set rather image. Much appreciate if that is in Excel.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
EliGohar
Partner - Creator III
Partner - Creator III
Author

Sure:

objectid customclassid positive negative
2 4 1 1
2 6 0 1
2 9 0 0
3 8 0 0
3 6 0 1

 

Thanks.

LRuCelver
Partner - Creator III
Partner - Creator III

I hate it, but it seems to work:

Data:
NoConcatenate Load * Inline [
	objectid,customclassid,positive,negative
    2,4,1,1
    2,6,0,1
    2,9,0,0
    3,8,0,0
    3,6,0,1
];

GenericTable:
Generic Load 
	objectid,
    customclassid,
    2 * positive + negative as state
Resident Data;

Classes: NoConcatenate Load Distinct customclassid Resident Data;
Table: NoConcatenate Load Null() as objectid AutoGenerate 0;

For vIndex = 0 to NoOfRows('Classes') - 1
	Let vClass = Peek('customclassid', $(vIndex), 'Classes');

	Join(Table) Load
    	objectid,
        Floor("$(vClass)" / 2) as "class_$(vClass)",
        Mod("$(vClass)", 2) as "not_class_$(vClass)"
    Resident "GenericTable.$(vClass)";
    
    Drop Table "GenericTable.$(vClass)";

Next vIndex
Let vIndex;
Let vClass;
Drop Tables Data, Classes;
EliGohar
Partner - Creator III
Partner - Creator III
Author

Nice one! Thanks!