the question is Region has its Subfields so the case study is as below

[sumitjadhav]

R1(Sb1,Sb2,Sb3)=data(1,5,7)

R2(Sb4)=data(9)

R3(Sb5)=data(10)

R4(Sb6,Sb7)=data(7,8)

R5(Sb8)=data(9)

[martynlloyd]

Solution (but you should rethink your strategy )

DataIn:
LOAD * INLINE [
    Region, Sub_Region, data,code
    R1, Sb1, 1,1
    R1, Sb2, 5,1
    R1, Sb3, 7,1
    R2, Sb4, 9,2
    R3, Sb5, 10,3
    R4, Sb6, 7,4
    R4, Sb7, 8,4
    R5, Sb8, 9,5
   ];

NewTable:
LOAD
Region & Sub_Region as SortKey
,Sub_Region as Dim
,data
Resident DataIn
;
Concatenate
LOAD
Region & 'ZZZ' as SortKey
,Region as Dim
,Sum(data) as data
Resident DataIn
Group by Region
;

[sasiparupudi1]

Please try

x:

LOAD * INLINE [

    Region, Sub_Region, data,code

    R1, Sb1, 1,1

    R1, Sb2, 5,1

    R1, Sb3, 7,1

    R2, Sb4, 9,2

    R3, Sb5, 10,3

    R4, Sb6, 7,4

    R4, Sb7, 8,4

    R5, Sb8, 9,5

   ];

NoConcatenate

Temp:

load Region,concat(Sub_Region,',') as xx,sum(data) as data Resident x Group by Region ;

NoConcatenate

region:

load Region,data Resident Temp;

NoConcatenate

subregions:

load subfield(xx,',') as Sub_Region Resident Temp;

left join(subregions)

load Region,Sub_Region, data as SubregionData Resident x;

drop Table x,Temp;

hth

Sasi