Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have this table:
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!
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 please share the data set rather image. Much appreciate if that is in Excel.
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.
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;
Nice one! Thanks!