Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
dmohanty
Partner
Partner

Cross Table to Straight Table?

Hi All,

I have a data table in an Excel File in this below type Cross Table, which I need to load in QV (Ignore the data)

Q1Q1Q1Q2Q2Q2
MarAprMayJunJulAug
IND000000
USA000000
JPN000000
CAN000000
UK000000
AUS000000

How to/What would be the script to write in QV to convert to Straight Table? Please note that there are two Horizontal Fields (Quarter and Month), and final straight table in QV should have 4 fields: Country, Quarter, Month and Sales?

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

You can try this code:

Table1:

CrossTable('Month', 'Sales')

LOAD *

FROM

Crosstable.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Table2:

Load *,

pick(match(Month, 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug'), 'Q1', 'Q1', 'Q1', 'Q2', 'Q2', 'Q2') as Quarter

Resident Table1;

Rename Field F1 TO Country;

Drop Table Table1;

I just loaded in the months and left out the quarter row, then calculated the quarter with an expression.

Hope this helps!

View solution in original post

12 Replies
jerem1234
Specialist II
Specialist II

You can try this code:

Table1:

CrossTable('Month', 'Sales')

LOAD *

FROM

Crosstable.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Table2:

Load *,

pick(match(Month, 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug'), 'Q1', 'Q1', 'Q1', 'Q2', 'Q2', 'Q2') as Quarter

Resident Table1;

Rename Field F1 TO Country;

Drop Table Table1;

I just loaded in the months and left out the quarter row, then calculated the quarter with an expression.

Hope this helps!

View solution in original post

phaneendra_kunc
Partner
Partner

Find the solution in attached. Code i have used is in below.

Table:

CrossTable(Country, Data, 2)

LOAD F1 as Quarter,

    F2 as Month,

    IND,

    JPN,

    CAN,

    UK,

    AUS

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Transpose()

));

Not applicable

Jeremiah Kurpat i test your solution it is failing to display quarters.

Not applicable

Phaneendra Kunche i believe you used the transpose transformation step and changed the quarters and months to rows and countries to colums and used them as qualifers and country as attribute.

phaneendra_kunc
Partner
Partner

yes that is true and simplest way i can think of.

jerem1234
Specialist II
Specialist II

Hmm seems to work in my example.

I do like Phaneendra Kunche's example.

Please find attached.

Not applicable

I believe so as resident table is a overhead interms of performance and memory usage and even though the transformation step consumes memory i believe it won't be a overhead for simple transformation like this.

dmohanty
Partner
Partner
Author

Hi Phaneendra,

Thank you much!

Seems quite a fine solution. But in case the Country field has some 100 records, it will be difficult to write the data manually. Any alternatives to deal with large set of records?

Not applicable

The solution what phanidhra suggest will not requires you to write all the names of the countries because the transpose function will automatically change them and you can find the transpose in Transformation before the crosstable wizard