swap/transpose date fields or dimensions at load scirpt

[Coloful_Black]

Hi experts:

Is there a way to swap /transpose the table like below into a second data structure shown at second table at loading scripts? 

( a simplified example)

Product	21-Jun	21-May	21-Apr	21-Mar	21-Feb	21-Jan	20-Dec	20-Nov	20-Oct
1	3320	3320	3320	3320	6640	1660	3320	3320	4980
2	0	0	0	0	0	0	0	0	0
3	0	0	0	836.2	0	836.2	0	0	836.2
4	0	0	0	580.1	580.1	1160.2	0	0	580.1
5	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	0	0	0	0
7	0	0	0	0	0	0	0	0	0
8	295.4	147.7	147.7	147.7	295.4	0	147.7	147.7	147.7
9	16342.3	907.9	0	2723.7	0	0	0	0	0
10	7551.1	3775.5	0	0	0	0	0	0	0
11	28249.5	20178.1	40356.4	40356.4	40356.4	40356.4	50445.5	49436.5	34302.9
12	30639.6	0	0	0	0	0	0	0	0
13	4171.4	1854	3244.5	2317.5	1390.5	4171.4	1854	2317.4	3707.9
14	6057.5	6057.5	0	0	0	0	0	0	0
15	0	0	0	0	0	0	0	0	0

preferred data structure table

Product	Date	Value
1	21-Jun	xxx
2	21-May	xxx
3	21-Apr	xxx
4	21-Mar	xxx
5	21-Feb	xxx
6	21-Jan	xxx
7	20-Dec	xxx
8	20-Nov	xxx
9	20-Oct	xxx
10	21-Jun	xxx
11	21-May	xxx
12	21-Apr	xxx
13	21-Mar	xxx
14	21-Feb	xxx
15	21-Jan	xxx

much apprecaited

[pedrobergo · Accepted solution]

Hi @Coloful_Black 

Suposed that 1st table is called ORIGINAL_TABLE, use the CrossTable command.

TransposedTable:
CrossTable(Date, Value,1 )
Load Product,
[21-Jun],
[21-May],
[21-Apr],
[21-Mar],
[21-Feb],
[21-Jan],
[20-Dec],
[20-Nov],
[20-Oct]
Resident ORIGINAL_TABLE;

[Coloful_Black]

thanks Pedro

just wanted to clarify the "1" you put at the crosstable function

I quickly checked on line, it says it is for the position for qualifier. 

is that qualifier usually refers to the key as well ? in this case , it is the dimension of product

and if my product field is not at the first position but in the second like this, I shall put 2 instead?

load 

state

product

Month 1

Month 2

....

[pedrobergo]

Hi @Coloful_Black ,

The qualifier means that Crosstable will not to pivot the 1st column at Load below Crosstable line, look that:

TransposedTable:
CrossTable(Date, Value,1 )
Load Product, <-- The 1st column will be maintained.
[21-Jun],  <-- The following lines will be pivoted using field names Date and Value
(..)

If you have two columns that would not be pivoted (state and product), just setup it up at Crosstable line.

TransposedTable:
CrossTable(Date, Value, 2 )
Load State, Product, <-- The 1st and 2nd columns will be maintained.
[21-Jun], <-- The following lines will be pivoted using field names Date and Value
(..)

[],

Pedro