Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pskumars
Partner
Partner

data loop and creating new field with existing field

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:

countryfunction sub function
xaa1
xbb1
xcc1
yaa1
ybb1
yaa1
zAllAll

 

 

expected result like below:

countryfunction sub function
xaa1
xbb1
xcc1
yaa1
ybb1
yaa1
zaa1
zbb1
zcc1
zaa2
zbb2
zcc2
zcc3
Labels (1)
3 Replies
OmarBenSalem
Partner
Partner

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;:

Capture.PNG

OmarBenSalem
Partner
Partner

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;