Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Coloful_Black
Contributor III
Contributor III

swap/transpose date fields or dimensions at load scirpt

 

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)

Product21-Jun21-May21-Apr21-Mar21-Feb21-Jan20-Dec20-Nov20-Oct
1332033203320332066401660332033204980
2000000000
3000836.20836.200836.2
4000580.1580.11160.200580.1
5000000000
6000000000
7000000000
8295.4147.7147.7147.7295.40147.7147.7147.7
916342.3907.902723.700000
107551.13775.50000000
1128249.520178.140356.440356.440356.440356.450445.549436.534302.9
1230639.600000000
134171.418543244.52317.51390.54171.418542317.43707.9
146057.56057.50000000
15000000000

 

preferred data structure table

ProductDateValue
121-Junxxx
221-Mayxxx
321-Aprxxx
421-Marxxx
521-Febxxx
621-Janxxx
720-Decxxx
820-Novxxx
920-Octxxx
1021-Junxxx
1121-Mayxxx
1221-Aprxxx
1321-Marxxx
1421-Febxxx
1521-Janxxx

 

much apprecaited

1 Solution

Accepted Solutions
pedrobergo
Employee
Employee

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;

 

View solution in original post

3 Replies
pedrobergo
Employee
Employee

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
Contributor III
Contributor III
Author

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
Employee
Employee

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