Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a dataset as attached in the excel and i want to know how to do a cross table to get the data in the desired format.
I tried different options in the edit script but not able to get the second rows as columns.
Any help on this please.
hi,
The syntax is:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
where:
attribute field name is the field to contain the attribute values.
data field name is the field to contain the data values.
n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.
Hi,
Try like this
Temp:
CrossTable(Subject_New, Value, 3)
LOAD If(Len(Trim(F1)) = 0, Previous(F1), F1) AS Region,
//If(Len(Trim(Subject)) = 0, Previous(Subject), Subject) AS Measure,
*
FROM
[Data (2).xlsx]
(ooxml, embedded labels, table is [Data Source], filters(
Transpose()
));
Data:
LOAD
Region,
Subject_New AS Subject,
Value AS Count
RESIDENT Temp
WHERE Subject= 'Count';
LEFT JOIN(Data)
Data:
LOAD
Region,
Subject_New AS Subject,
Value AS Avg
RESIDENT Temp
WHERE Subject= 'Avg';
DROP TABLE Temp;
Check this thread , this will help you to achieve your requirement
Hi,
One more solution using multi_header_pivot_import.qvw
You just need to change below mentioned statements only. That's it