Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

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

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 

LOCATIONMetricsBrelinBrandenburgChinaAustraliaMangoliaRankSouthAfricaKenya
AfricaTax00000 2010
AfricaFund00000 4015
AfricaGross00000 5080
Africa 000001000
AsiaTax008210065 00
AsiaFund00599362 00
AsiaGross00859592 00
Asia 00000800
GermanyTax5482000 00
GermanyFund5471000 00
GermanyGross8491000 00
Germany 00000600

 

Output Table 

LOCATIONMetricsBrelinBrandenburgChinaAustraliaMangoliaRankSouthAfricaKenya
AfricaTax00000 2010
AfricaFund00000 4015
AfricaGross00000 5080
Africa 000001000
AsiaTax008210065 00
AsiaFund00599362 00
AsiaGross00859592 00
Asia 00000800
GermanyTax5482000 00
GermanyFund5471000 00
GermanyGross8491000 00
Germany 00000600
BrelinTax540000 00
BrelinFund540000 00
BrelinGross840000 00
Brelin 00000 00
BrandenburgTax082000 00
BrandenburgFund071000 00
BrandenburgGross091000 00
Brandenburg 00000 00

 

Hope This Pictorial helps , to make above requirement clear .

ctor.PNG

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 .

4 Replies
shekhar_analyti
Specialist
Specialist
Author

 

Hi Sunny Bhai , please help .

@sunny_talwar  

shekhar_analyti
Specialist
Specialist
Author

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Specialist
Specialist
Author

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') ;