Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;:
Still, I can't seem to understand where did the a2 a3 b2 c2 ... values came from?
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;