create a new table in script by manipulating an existing table

[#1]

I have an existing table with the columns as seen below (there are many more columns)

ID	Q1	Q2	Q3	Q4
1	5	2	3	2
2	15	10	10	8

I need to create a new table in script as follows:

ID	Quarter	Amount
1	Q1	5
2	Q1	15
1	Q2	2
2	Q2	10
1	Q3	3
2	Q3	10
1	Q4	2
2	Q4	8

where the Quarter column tells me which Quarter based on the column header in the first table (Q1, Q2, etc) and Amount captures the number value in that column.

I have no idea where to even begin with this, please help

[ecolomer · Accepted solution]

Is this??

The script:

DATA:

LOAD * Inline [

ID, Q1, Q2, Q3, Q4

1, 5, 2, 3, 2

2, 15, 10, 10, 8

];

D_Q:

Load ID, 'Q1' as Quarter, Q1 as Value

resident DATA;

Load ID, 'Q2' as Quarter, Q2 as Value

resident DATA;

Load ID, 'Q3' as Quarter, Q3 as Value

resident DATA;

Load ID, 'Q4' as Quarter, Q4 as Value

resident DATA;

[rob_insley]

Hi Shana,

A Cross Table is what you need.

TEmpData:

CrossTable(Quarter, Amount,1)

LOAD ID,

     Q1,

     Q2,

     Q3,

     Q4

FROM

C:\ProgramData\QlikTech\QlikView\QVExample.xlsx

(ooxml, embedded labels);

Thanks,

Rob

[#4]

Thank you Enrique this worked perfectly!!

[#5]

Thank you for your response Rob, this solution worked but how would I manipulate the numbers for this option? For example, I need 3 amounts - amount/1000, amount/1000*.2, and amount/1000*.4.

It also loaded all rows of the table but my original load only loads specific rows. how would I get around this?