Splitting out value into two

[#1]

Hi guys ... I'm trying to split out the 'Region' America as highlighted in bold into America AB and America AS so effectively 'America' would no longer exist as a 'Region' but instead America AB and America AS per the result at the bottom. What would be the most effective way to do this :

LOAD * INLINE [

Region,     SubRegion,           Sales

America,  America AB,       10000

America,  America AS,        500

Asia TY, India,              6000

Asia AS, Nepal,              500

Africa CS,  Morocco,           4000

];

Result :

Region,     SubRegion,           Sales

America AB,  America AB,       10000

America AS,  America AS,        500

Asia TY, India,              6000

Asia AS, Nepal,              500

Africa CS,  Morocco,           4000

[sunny_talwar]

I guess an if Statement can work:

If(Region = 'America', SubRegion, Region) as Region

Full Script:

Table:

LOAD If(Trim(Region) = 'America', SubRegion, Region) as Region,

  SubRegion,

  Sales;

LOAD * INLINE [

Region,     SubRegion,           Sales

America,  America AB,       10000

America,  America AS,        500

Asia TY, India,              6000

Asia AS, Nepal,              500

Africa CS,  Morocco,           4000

];