Nested If-statements are sometimes necessary in QlikView- load-scripts, unfortunately.
They are quite difficult to read and very tricky to create and especially to debug.
In the last recent months I had a lot of projects where a tenfold nesting of if-statements was not uncommon.
After struggling with these nested ifs for a while I decided to create a little user-interface which helped me to decrease the time for creating nested if-statements.
Even if this tools is just a little, little helper it may also be helpful for you, so I decided to publish it here:
Try the “Nested If Generator” now
Some screenshots and explanation:
At the beginning you can define the “fieldname” to be generated” and define your first if-statement:
Let’s assume you are checking the two fields “Age” and “Profession”:
Defining the first if-statement
If you want to add an additional if-clause just click on “Add another IF-block”:
Adding a second if-statement
As you can see when comparing the two screenshots above the “else-block” of the first statement was removed and replaced by an additional if-clause.
After some further if-blocks the configuration could look like as follows:
After adding several if-statements
Now the last step is easy, change to the “Generated Code” tab and copy your code:
Generated Code
The Tool is offering three different “Formatting Styles”:
Single line formatting
Single line formatting
If-statements formatted like this were the main-reason for creating this tool 
Indent style with one line per condition
Indent style with one line per condition
This is my preferred formatting-style for really large if-statements with complex conditions
Indent style with one line per if-statement
Indent style with one line per if-statement
This is my preferred formatting-style for if-statements with short conditions like demonstrated in this example …
Have fun
Stefan WALTHER
27 Comments
That’s cool
Good work – even if I’d wish there would also be an option for importing existing if statements …
V.
Hi Vladimir,
thanks for your comment.
Yes, you are right, this would be a nice feature and really, really useful. I have also tried to implement this, but unfortunately parsing an existing nested-if is not that easy as it seems to be. Therefore I would need a script-parser (or develop) one … and this is not an easy task, especially by using only Javascript (I have develped the little tool only with Html, CSS and JS to be platform independent) …
Unfortunately, but maybe I will at any future time have the clue how to code this …
Best regards
Stefan
Hi Stefan,
thanks for your feedback … yes, I see … did not think about the complexity when having to parse existing if-statements …
But i really hope you will give the realization of this additional feature a second try. I have used your tool several times within the last days, and it is really nice. But when having to edit existing statements your tool unfortunately does not really help because one would need to import an existing if …
So I really hope that I will soon (or even bette sooner) read here about the new feature “Importing existing if-statements” …
)
Nethertheless, thank you very much for your efforts!!!! Saves me a lot of time …
V.
Very good!
Very good and useful work!
Stefan,
I was thinking about doing something similar for the valuelist()-function. Do you have any plans to extend your app in this direction?
The function is sometimes useful when it comes to financial reporting (where you have generic dimension-values that are nowhere defined in the datamodel). However the syntax is more than horrible
Small example:
if (
valuelist('Vorjahr','Gewähltes Jahr', 'Differenz') = 'Vorjahr',
sum(if (inyeartodate(Bestelldatum,Stichtag,-1),Anzahl)),
if (
valuelist('Vorjahr','Gewähltes Jahr', 'Differenz') = 'Gewähltes Jahr',
sum(if (inyeartodate(Bestelldatum,Stichtag,0),Anzahl)),
sum(if (inyeartodate(Bestelldatum,Stichtag,0),Anzahl)) - sum(if (inyeartodate(Bestelldatum,Stichtag,-1),Anzahl))
)
)
Hi rva (Roland
),
interesting idea, not really thought about that.
I am sure this would be a nice improvement for the tool, but at the moment I do not really have the clue how this should ideally included.
Do you have any concrete ideas how this could look like? If so we could make a deal: If you give me some further hints how the valuelist could be included I’ll do the coding work
Best regards
Stefan
I have some ideas, but I need to bring it to paper!
Give me some time
Sure …
Its really very good..!!
Good work….I appreciate that…!
Hi Stefan,
this is a very interesting approach! This could be extended to handle business rules in QV. Did you experience some limitations in nesting depth or line size?
- Ralf
Hi Ralf,
I had absolutely no problems with a nested if with 10 levels but I did not invest time to find some limits of the if-statements in QV.
Yes, I am using this for business-rules, but I am very interested how you would like to have the tool extended to support business rules!!! As also suggested above, if you giving me some more information on some requirements for business-rules (even maybe examples) I would happily do the coding work
Best regards
Stefan
Hi Stefan,
I’m thinking about a script generator inside QV where you can use the loaded data structure (all loaded fields) for selection of conditions in combination with a similar IF constructor. Every condition (rule) would create a flag (1/0 for false/true) which can be summarized. The combination of conditions (rules) could be lead into new flags by multiplying (flag1 * flag2).
A second stacked LOAD statement can aggregate all flags and measure the severity of each rule
The IF nesting would be a good thing on top.
Kind Regards,
Ralf
Hi Ralf,
thanks for your detailled answer.
from me.
And a big
Yes, those features would be nice and would be a real improvement for developing QV-applications.
And I have so many similar ideas in mind, but as you know, unfortunately QV does not offer a plugin- (or AddIn) mechanism for the IDE like other vendors or tools are offering (like Visual Studio …). Unfortunately …
I know, some offline-tools like the one I have created are not the best way and they can only help in some (very limited) situations … unfortunately again …
So maybe, maybe in the future we can think about those features again …
Best regards
Stefan
Stefan,
there is no problem to do things like that in QV. We did it in TIQView for data profiling.
You can display the fields for selection and build script code from that selection and input variables (for expressions e.g.). Then, you could include this piece of code as external script
$(Include = filename.qvs)or interpret a whole load statement in a variable just like this:
table:$(load_statement);
So, I see no problem except some efforts…
- Ralf
Hi Ralf again,
Hmmm, yes sure, you are right … interesting discussion which brings me to new ideas … Yes, maybe I should really try to bring my mindset away from tools like Visual Studio torwards to the possibilities available in QlikView …
Interesting … so far I have just thougt that can’t be the way some tools/add-ins should be realised … but maybe …
I will try TIQView within in the next days to have a look how you’ve manged this …
Thanks for your feedback, that was one of the main reasons I have switched my blog to English (even if we could have also made this discussion in German, isn’t it
)
Best regards
Stefan
Hi all,
I’m coming back on this because I’ve worked out a solution for a interactive way to check rules on the data.
We’ve included this in our TIQView community edition which can be downloaded here: http://bit.ly/TIQView
You can choose 1 to 3 fields for rule evaluation and you can use selections and search expressions for
getting the result set.
Cheers,
Ralf
Very good!
What about expressions where the immediate expression is also an if statement. For example:
if([Número Tipo Fluxo]=0,
if([Número Tipo Documento]=1,
‘Cheque’,
if([Número Tipo Documento]=0,
‘Duplicata’,
[Item Fluxo]
)
)
,
[Item Fluxo]
)
Yes, you are right … my tool does not directly support this … but does it really make a difference?
Best regards
Stefan
Congratulations!
This tool is really helpful!
Thanks
Darci
Thanks, nice to hear that!
Just for your information: I am just working on an improved version of this tool. If you have any wishes or ideas, please let me know!
Best regards
Stefan
Hi Stephen,
As a newcomer to Qlikview I wanted to thank you for this little tool.
Makes life so much easier!
Regards….
Nice to hear/read that!
Regards
Stefan
Hello.
I wrote this conditional statment but seems it’s not working correctly.
It is supposed to be used in an excel cell, Perhaps you could show me where I am wrong.
=IF(1000-5999,20%,IF(6000-11999,25%,IF(12000-25999,30%,IF(26000-99999,35%,Please Contact your Sales Rep))))
The value of the total of the order [ Cell R2 ]
The discount rate according the the total of the order. [ Cell R3 ]
It needs to do this.
If value in Cell R2 is
Between 1000-5999 Then the value of the cell is 20%
Between 6000-11999 Then the value of the cell is 25%
Between 12000-25999 Then the value of the cell is 30%
Between 26000-119999 Then the value of the cell is 35%
is 120000 then the value of the cell is 37%
This is for Excel 2010
Could someone please help.
Thanks.
Additional to that, the error I’m getting is circular reference.
Here is the Excel file for you to see: https://www.yousendit.com/download/TEhYUWVoZ1A0b0RIRHNUQw
Could someone please help.
Thanks.
This is very useful..Thanks a lot for sharing.
4 Trackbacks
[...] You can check it out here [...]
[...] Possibly related posts:A little tool for creating nested if-statements [...]
[...] QlikViewQlikView Publisher/ServerPublishing more tools for the daily work with QlikView like the “Nested if-generator tool”, which is used by visitors of qlikblog.at more than 100-times used a day (!!!!)QlikView APIsRelated [...]
[...] http://www.qlikblog.at/464/tool-creating-nested-ifstatements/ [...]