Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Cross table loading into qlikview

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.

4 Replies
Highlighted
Champion III
Champion III

Re: Cross table loading into qlikview

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.


Highlighted
MVP & Luminary
MVP & Luminary

Re: Cross table loading into qlikview

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;

Highlighted

Re: Cross table loading into qlikview

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

multi_header_pivot_import.qvw

Highlighted

Re: Cross table loading into qlikview

Hi,

One more solution using  multi_header_pivot_import.qvw

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

Capture1.JPG

Capture1.JPG