Cross table loading into qlikview

[sarathi_pm]

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.

[Chanty4u]

hi,

Cross Table Load

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.

[jagan]

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;

[avinashelite]

Check this thread , this will help you to achieve your requirement

multi_header_pivot_import.qvw

[settu_periasamy]

Hi,

One more solution using  multi_header_pivot_import.qvw‌

You just need to change below mentioned statements only. That's it