topic Re: Cross table loading into qlikview in QlikView

Cross table loading into qlikview

sarathi_pm — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Cross table loading into qlikview

Chanty4u — Tue, 22 Mar 2016 06:27:39 GMT

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.

Re: Cross table loading into qlikview

jagan — Tue, 22 Mar 2016 06:53:55 GMT

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;

Re: Cross table loading into qlikview

avinashelite — Tue, 22 Mar 2016 06:56:14 GMT

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

multi_header_pivot_import.qvw

Re: Cross table loading into qlikview

settu_periasamy — Tue, 22 Mar 2016 07:07:50 GMT

Hi,

One more solution using multi_header_pivot_import.qvw‌

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