topic Re: Cross Table to Straight Table? in QlikView

Cross Table to Straight Table?

dmohanty — Tue, 16 Sep 2014 17:00:16 GMT

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)

	Q1	Q1	Q1	Q2	Q2	Q2
	Mar	Apr	May	Jun	Jul	Aug
IND	0	0	0	0	0	0
USA	0	0	0	0	0	0
JPN	0	0	0	0	0	0
CAN	0	0	0	0	0	0
UK	0	0	0	0	0	0
AUS	0	0	0	0	0	0

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?

Re: Cross Table to Straight Table?

jerem1234 — Tue, 16 Sep 2014 17:22:46 GMT

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!

Re: Cross Table to Straight Table?

phaneendra_kunc — Tue, 16 Sep 2014 18:16:56 GMT

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()

));

Re: Cross Table to Straight Table?

Tue, 16 Sep 2014 18:19:17 GMT

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

Re: Cross Table to Straight Table?

Tue, 16 Sep 2014 18:20:58 GMT

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.

Re: Cross Table to Straight Table?

phaneendra_kunc — Tue, 16 Sep 2014 18:26:47 GMT

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

Re: Re: Cross Table to Straight Table?

jerem1234 — Tue, 16 Sep 2014 18:28:28 GMT

Hmm seems to work in my example.

I do like Phaneendra Kunche's example.

Please find attached.

Re: Cross Table to Straight Table?

Tue, 16 Sep 2014 18:30:55 GMT

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.

Re: Cross Table to Straight Table?

dmohanty — Tue, 16 Sep 2014 18:36:20 GMT

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?

Re: Cross Table to Straight Table?

Tue, 16 Sep 2014 18:40:21 GMT

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

Re: Cross Table to Straight Table?

phaneendra_kunc — Tue, 16 Sep 2014 23:24:24 GMT

You dont need to manually enter the country names in your script. What i did is transpose your data-set before using cross table wizard.

if your country list will change every time you reload, i would suggest to read all country names with comma separated first and create a variable so that you can use it in your load statement.

Try this script in the same app i have shared.

Temp:

Load Concat(Countries,',') as FieldList;

LOAD Distinct F1 as Countries

FROM

C:\Users\ip1858\Desktop\Book1.xlsx

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

let vFields = peek('FieldList',0,Temp);

Drop table Temp;

Table:

CrossTable(Country, Data, 2)

LOAD F1 as Quarter,

F2 as Month,

$(vFields)

FROM

C:\Users\ip1858\Desktop\Book1.xlsx

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

Transpose()

));

Re: Cross Table to Straight Table?

jerem1234 — Wed, 17 Sep 2014 13:09:22 GMT

Why not use *? You should be able to get away with:

Table:

CrossTable(Country, Data, 4)

LOAD F1 as Quarter,

F2 as Month,

FROM

Crosstable.xlsx

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

Transpose()

));

Drop Field F1, F2 from Table;

Hope this helps!

Re: Cross Table to Straight Table?

Fri, 19 Sep 2014 09:39:28 GMT

DMohanty please mark the thread as answered if you believe that you query is answered so that people can work on other quereys.

Thanks

SKC

	Q1	Q1	Q1	Q2	Q2	Q2
	Mar	Apr	May	Jun	Jul	Aug
IND	0	0	0	0	0	0
USA	0	0	0	0	0	0
JPN	0	0	0	0	0	0
CAN	0	0	0	0	0	0
UK	0	0	0	0	0	0
AUS	0	0	0	0	0	0

	Q1	Q1	Q1	Q2	Q2	Q2
	Mar	Apr	May	Jun	Jul	Aug
IND	0	0	0	0	0	0
USA	0	0	0	0	0	0
JPN	0	0	0	0	0	0
CAN	0	0	0	0	0	0
UK	0	0	0	0	0	0
AUS	0	0	0	0	0	0

	Q1	Q1	Q1	Q2	Q2	Q2
	Mar	Apr	May	Jun	Jul	Aug
IND	0	0	0	0	0	0
USA	0	0	0	0	0	0
JPN	0	0	0	0	0	0
CAN	0	0	0	0	0	0
UK	0	0	0	0	0	0
AUS	0	0	0	0	0	0