Cross Table to Straight Table?

[dmohanty]

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?

[phaneendra_kunc]

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

));

[jerem1234]

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!

[#4]

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