How to achieve "selected" column to row transform without changing source data structure ?

[shekhar_analyti]

Hello All ,

I have a case where i want to selectively transform few columns into row , without changing existing table structure through script . 

Source Table 

LOCATION	Metrics	Brelin	Brandenburg	China	Australia	Mangolia	Rank	SouthAfrica	Kenya
Africa	Tax	0	0	0	0	0		20	10
Africa	Fund	0	0	0	0	0		40	15
Africa	Gross	0	0	0	0	0		50	80
Africa		0	0	0	0	0	10	0	0
Asia	Tax	0	0	82	100	65		0	0
Asia	Fund	0	0	59	93	62		0	0
Asia	Gross	0	0	85	95	92		0	0
Asia		0	0	0	0	0	8	0	0
Germany	Tax	54	82	0	0	0		0	0
Germany	Fund	54	71	0	0	0		0	0
Germany	Gross	84	91	0	0	0		0	0
Germany		0	0	0	0	0	6	0	0

Output Table 

LOCATION	Metrics	Brelin	Brandenburg	China	Australia	Mangolia	Rank	SouthAfrica	Kenya
Africa	Tax	0	0	0	0	0		20	10
Africa	Fund	0	0	0	0	0		40	15
Africa	Gross	0	0	0	0	0		50	80
Africa		0	0	0	0	0	10	0	0
Asia	Tax	0	0	82	100	65		0	0
Asia	Fund	0	0	59	93	62		0	0
Asia	Gross	0	0	85	95	92		0	0
Asia		0	0	0	0	0	8	0	0
Germany	Tax	54	82	0	0	0		0	0
Germany	Fund	54	71	0	0	0		0	0
Germany	Gross	84	91	0	0	0		0	0
Germany		0	0	0	0	0	6	0	0
Brelin	Tax	54	0	0	0	0		0	0
Brelin	Fund	54	0	0	0	0		0	0
Brelin	Gross	84	0	0	0	0		0	0
Brelin		0	0	0	0	0		0	0
Brandenburg	Tax	0	82	0	0	0		0	0
Brandenburg	Fund	0	71	0	0	0		0	0
Brandenburg	Gross	0	91	0	0	0		0	0
Brandenburg		0	0	0	0	0		0	0

Hope This Pictorial helps , to make above requirement clear .

Source Data

LOAD * INLINE [

Organisation, LOCATION, Metrics, Brelin, Brandenburg, China, Australia, Mangolia, Rank, SouthAfrica, Kenya
Tata, Africa, Tax, 0, 0, 0, 0, 0, , 20, 10
Tata, Africa, Fund, 0, 0, 0, 0, 0, , 40, 15
Tata, Africa, Gross, 0, 0, 0, 0, 0, , 50, 80
Tata, Africa, , 0, 0, 0, 0, 0, 10, 0, 0
Tata, Asia, Tax, 0, 0, 82, 100, 65, , 0, 0
Tata, Asia, Fund, 0, 0, 59, 93, 62, , 0, 0
Tata, Asia, Gross, 0, 0, 85, 95, 92, , 0, 0
Tata, Asia, , 0, 0, 0, 0, 0, 8, 0, 0
Tata, Germany, Tax, 54, 82, 0, 0, 0, , 0, 0
Tata, Germany, Fund, 54, 71, 0, 0, 0, , 0, 0
Tata, Germany, Gross, 84, 91, 0, 0, 0, , 0, 0
Tata, Germany, , 0, 0, 0, 0, 0, 6, 0, 0
AXA, Africa, Tax, 0, 0, 0, 0, 0, , 30, 10
AXA, Africa, Fund, 0, 0, 0, 0, 0, , 40, 50
AXA, Africa, Gross, 0, 0, 0, 0, 0, , 80, 80
AXA, Africa, , 0, 0, 0, 0, 0, 2, 0, 0
AXA, Asia, Tax, 0, 0, 100, 100, 200, , 0, 0
AXA, Asia, Fund, 0, 0, 50, 70, 20, , 0, 0
AXA, Asia, Gross, 0, 0, 25, 25, 25, , 0, 0
AXA, Asia, , 0, 0, 0, 0, 0, 1, 0, 0
AXA, Germany, Tax, 11, 15, 0, 0, 0, , 0, 0
AXA, Germany, Fund, 20, 100, 0, 0, 0, , 0, 0
AXA, Germany, Gross, 40, 90, 0, 0, 0, , 0, 0
AXA, Germany, , 0, 0, 0, 0, 0, 11, 0, 0
];

Thank You all .

[shekhar_analyti]

Hi Sunny Bhai , please help .

@sunny_talwar  

[shekhar_analyti]

@Kushal_Chawda 
@marcus_sommer 

[marcus_sommer]

I suggest to transform the whole table and not doing a mixmatch of different data-structures. Such transforming could be done with The-Crosstable-Load .

- Marcus

[shekhar_analyti]

Not sure , if this is right way ;

Data1:
LOAD * INLINE [

Organisation, LOCATION, Metrics, Brelin, Brandenburg, China, Australia, Mangolia, Rank, SouthAfrica, Kenya
Tata, Africa, Tax, 0, 0, 0, 0, 0, , 20, 10
Tata, Africa, Fund, 0, 0, 0, 0, 0, , 40, 15
Tata, Africa, Gross, 0, 0, 0, 0, 0, , 50, 80
Tata, Africa, , 0, 0, 0, 0, 0, 10, 0, 0
Tata, Asia, Tax, 0, 0, 82, 100, 65, , 0, 0
Tata, Asia, Fund, 0, 0, 59, 93, 62, , 0, 0
Tata, Asia, Gross, 0, 0, 85, 95, 92, , 0, 0
Tata, Asia, , 0, 0, 0, 0, 0, 8, 0, 0
Tata, Germany, Tax, 54, 82, 0, 0, 0, , 0, 0
Tata, Germany, Fund, 54, 71, 0, 0, 0, , 0, 0
Tata, Germany, Gross, 84, 91, 0, 0, 0, , 0, 0
Tata, Germany, , 0, 0, 0, 0, 0, 6, 0, 0
AXA, Africa, Tax, 0, 0, 0, 0, 0, , 30, 10
AXA, Africa, Fund, 0, 0, 0, 0, 0, , 40, 50
AXA, Africa, Gross, 0, 0, 0, 0, 0, , 80, 80
AXA, Africa, , 0, 0, 0, 0, 0, 2, 0, 0
AXA, Asia, Tax, 0, 0, 100, 100, 200, , 0, 0
AXA, Asia, Fund, 0, 0, 50, 70, 20, , 0, 0
AXA, Asia, Gross, 0, 0, 25, 25, 25, , 0, 0
AXA, Asia, , 0, 0, 0, 0, 0, 1, 0, 0
AXA, Germany, Tax, 11, 15, 0, 0, 0, , 0, 0
AXA, Germany, Fund, 20, 100, 0, 0, 0, , 0, 0
AXA, Germany, Gross, 40, 90, 0, 0, 0, , 0, 0
AXA, Germany, , 0, 0, 0, 0, 0, 11, 0, 0
];

LOAD
Organisation,
'Brelin' as LOCATION,
Metrics,
Brelin,
0 as Brandenburg,
0 as China,
0 as Australia,
0 as Mangolia,
Null() as Rank,
0 as SouthAfrica,
0 as Kenya Resident Data1 Where Match(LOCATION,'Germany') ;

LOAD
Organisation,
'Brandenburg' as LOCATION,
Metrics,
0 as Brelin,
Brandenburg,
0 as China,
0 as Australia,
0 as Mangolia,
Null() as Rank,
0 as SouthAfrica,
0 as Kenya Resident Data1 Where Match(LOCATION,'Germany') ;