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?

[jerem1234 · Accepted solution]

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!

[phaneendra_kunc]

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

));

[#4]

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

[#5]

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]

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

[jerem1234]

Hmm seems to work in my example.

I do like Phaneendra Kunche's example.

Please find attached.

[#8]

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]

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?

[#10]

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