data loop and creating new field with existing field

[pskumars]

Hello folks ,

i have data set like below from db.

now if i have  function and sub function as All .. i need create all possible values for respective country..

source data:

country	function	sub function
x	a	a1
x	b	b1
x	c	c1
y	a	a1
y	b	b1
y	a	a1
z	All	All

expected result like below:

country	function	sub function
x	a	a1
x	b	b1
x	c	c1
y	a	a1
y	b	b1
y	a	a1
z	a	a1
z	b	b1
z	c	c1
z	a	a2
z	b	b2
z	c	c2
z	c	c3

[OmarBenSalem]

This is as far as I could get for now, maybe @sunny_talwar  could help u more

Try to adapt this to ur script:

t:
load * Inline [
country, function, subfunction
x,a ,a1
x,b ,b1
x,c ,c1
y,a ,a1
y,b ,b1
y,a ,a1
z,All, All
];

NoConcatenate
toUse:
load * Resident t where function<>'All';
let vNbrRow=NoOfRows('toUse');

for i=0 to $(vNbrRow)-1;

let vFunction$(i)=peek('function',$(i),'toUse');
let vSubFunction$(i)=peek('subfunction',$(i),'toUse');

final0:
load '' as country, '' as function1,'' as subfunction1 AutoGenerate 1;
Concatenate(final0)

load country, '$(vFunction$(i))' as function1,'$(vSubFunction$(i))' as subfunction1 Resident t Where function='All';

next i

drop Tables toUse;

NoConcatenate
Final:
Load * Resident t Where function<>'All';

Drop Table t;
Concatenate(Final)
load country,function1 as function, subfunction1 as subfunction Resident final0 Where function1<>'';

Drop Table final0;

result;:

[OmarBenSalem]

Still, I can't seem to understand where did the a2 a3 b2 c2 ... values came from?

[sunny_talwar]

May be something like this

Table:
LOAD * INLINE [
    country, function, subfunction
    x, a, a1
    x, b, b1
    x, c, c1
    y, a, a1
    y, b, b1
    y, a, a1
    z, All, All
];

tmpTable:
LOAD country
Resident Table
Where function = 'All';

Left Join (tmpTable)
LOAD function,
	 subfunction
Resident Table
Where function <> 'All';

Concatenate (tmpTable)
LOAD country,
	 function,
	 subfunction
Resident Table
Where function <> 'All';

FinalTable:
NoConcatenate
LOAD country,
	 function,
	 If(country = 'z' and function = Previous(function),
	 If(subfunction = Previous(subfunction), (Left(subfunction, 1) & (Right(Peek('subfunction'), 1)+1)), subfunction), subfunction) as subfunction
Resident tmpTable
Order By country, function, subfunction;
	 
DROP Tables Table, tmpTable;