Dynamic If Statement

[melissapluke]

Hi all,

I have a script that is creating a dynamic field name by looping. See screenshot for "original_claim$(aa)". 

In my sample data, I know that the loop happened 12 times, so I created the "FindingMostRecent_Step2" table by using that knowledge. Going forward, with live data, I won't know how many times something looped in a given instance. I'd like to be able to create the "Step 2" table dynamically, using variables somehow, but I can't seem to figure it out. Any ideas?

Thanks,

Melissa

[stevejoyce]

@melissapluke  you can do this.  

I replied in a post a couple days ago and I did something similar you can take a look at.  In your do loop, dynamically create a variable that contains your whole if(len ... statement.  you'll keep updating it each loop with let vFieldCondition  = $(vFieldCondition) & 'new logic'

https://community.qlik.com/t5/App-Development/Create-combinations-of-a-set/m-p/1832646#M68723

I pulled out only a few lines in that post that you can focus on.  In my case i had an initial value to variable.  If you don't, or the first loop is different (because you don't want to start variable with '& ' & 'your condition', you do an if-statement based on len($(vcombination_calc)).  In my case i was concatenating n number of fields like //items1 & ',' & items2 & ',' & itemsN as combination.

I didn't disect your logic, but this is doable like below.  If you have an issue getting the right result you can reply back.

//initial syntax
set vcombination_calc = items1;

//after first syntax
let vcombination_calc = '$(vcombination_calc)' & ' & ' & chr(39) & ',' & chr(39) & '& items' & $(i) ;

,$(vcombination_calc) as combination

[melissapluke · Accepted solution]

This works perfectly. Thank you for your help!

[rajeshwar1]

Hi Melissa,

Can you share the script and how did you solve this problem. Since i am facing similar issue

[melissapluke]

Sure, here you go:

//this is finding the claim that was before it in the series
set aa=2;
let vfinallevelifstatement1='1';
let voriginalclaimifstatement1='"1_Control Number"';
//////////////////
//beginning of loop
//////////////////
Do while aa<b;
left Join (FindingMostRecent_Step1)
Load Distinct
"$(aa)_Control Number" as "1_Control Number",
"1_Control Number" as "original_claim$(aa)",
$(aa) as "level$(aa)"
resident FindingMostRecent_Step1;
let vfinallevelifstatement='if(len(level$(aa))>0,level$(aa),'&'$(vfinallevelifstatement1)'&')';
let vfinallevelifstatement1='$(vfinallevelifstatement)';
let voriginalclaimifstatement='if(len(original_claim$(aa))>0,original_claim$(aa),'&'$(voriginalclaimifstatement1)'&')';
let voriginalclaimifstatement1='$(voriginalclaimifstatement)';
let aa=aa+1;
Loop

//////////////////
//end of loop
//////////////////

[rajeshwar1]

Thanks Melissa!!

Could you share what's table FindingMostRecent_Step1 where left join is happening and what's Variable "b" as i am not getting what it is