A little tool for creating nested if-statements

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

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

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

After adding several if-statements

Now the last step is easy, change to the “Generated Code” tab and copy your code:

Generated Code to be used in QlikView

Generated Code

The Tool is offering three different “Formatting Styles”:

Single line formatting

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

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

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

Bookmark and Share

and tagged , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

37 Comments

  1. Vladimir Sconza
    Posted January 11, 2010 at 12:08 | Permalink

    That’s cool :)
    Good work – even if I’d wish there would also be an option for importing existing if statements …

    V.

    • Posted January 12, 2010 at 15:42 | Permalink

      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

      • Vladimir Sconza
        Posted January 14, 2010 at 08:55 | Permalink

        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.

  2. askyer
    Posted January 11, 2010 at 15:39 | Permalink

    Very good!

  3. countnazgul
    Posted January 12, 2010 at 09:39 | Permalink

    Very good and useful work! :-)

  4. rva
    Posted January 13, 2010 at 00:00 | Permalink

    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))
    )
    )

    • Posted January 13, 2010 at 00:16 | Permalink

      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

  5. rva
    Posted January 13, 2010 at 20:57 | Permalink

    I have some ideas, but I need to bring it to paper!
    Give me some time :)

  6. Silky
    Posted January 20, 2010 at 07:09 | Permalink

    Its really very good..!!
    Good work….I appreciate that…!

  7. Posted January 20, 2010 at 09:40 | Permalink

    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

    • Posted January 20, 2010 at 09:54 | Permalink

      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

      • Posted January 25, 2010 at 17:01 | Permalink

        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

        • Posted January 25, 2010 at 17:12 | Permalink

          Hi Ralf,

          thanks for your detailled answer.
          And a big :( :( :( from me.

          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

          • Posted January 25, 2010 at 17:28 | Permalink

            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

          • Posted January 25, 2010 at 17:39 | Permalink

            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

          • Posted October 27, 2010 at 20:13 | Permalink

            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

  8. o
    Posted March 24, 2010 at 04:22 | Permalink

    Very good!

  9. Cristian Guedes
    Posted April 30, 2010 at 15:52 | Permalink

    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]

    )

    • Posted October 28, 2010 at 00:54 | Permalink

      Yes, you are right … my tool does not directly support this … but does it really make a difference?

      Best regards
      Stefan

  10. Darci
    Posted October 27, 2010 at 15:37 | Permalink

    Congratulations!
    This tool is really helpful!

    Thanks

    Darci

    • Posted October 27, 2010 at 15:50 | Permalink

      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

  11. Adamantium
    Posted August 16, 2011 at 10:55 | Permalink

    Hi Stephen,

    As a newcomer to Qlikview I wanted to thank you for this little tool.
    Makes life so much easier! :-)

    Regards….

  12. Steven
    Posted October 13, 2012 at 09:30 | Permalink

    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.

  13. Seema
    Posted November 14, 2013 at 12:54 | Permalink

    This is very useful..Thanks a lot for sharing.

  14. Posted December 22, 2013 at 17:45 | Permalink

    This site was… how do I say it? Relevant!!
    Finally I’ve found something which helped me. Kudos!

  15. Posted January 6, 2014 at 12:14 | Permalink

    Hmm it appears like your blog ate my first comment (it was extremely long) so I guess I’ll just sum it up what
    I submitted and say, I’m thoroughly enjoying your blog.

    I too am an aspiring blog blogger but I’m still
    new to the whole thing. Do you have any tips for rookie blog writers?
    I’d certainly appreciate it.

  16. Posted January 12, 2014 at 18:40 | Permalink

    Everyone loves it when people come together and share ideas.

    Great blog, stick with it!

  17. Posted January 13, 2014 at 00:01 | Permalink

    Having read this I thought it was extremely informative.
    I appreciate you taking the time and energy to put this article together.
    I once again find myself personally spending way too much time both reading and leaving
    comments. But so what, it was still worth it!

  18. Posted February 10, 2014 at 01:21 | Permalink

    Everything is very open with a really cler description of the challenges.

    It wwas really informative. Your website is very useful.
    Many thanks for sharing!

  19. Posted June 18, 2014 at 18:15 | Permalink

    Very shortly this web page will be famous among
    all blogging people, due to it’s good articles or reviews

  20. Posted June 21, 2014 at 07:35 | Permalink

    Heya i am for the first time here. I came across this board and I find It truly useful
    & it helped me out much. I hope to give something back and help others
    like you aided me.

    My site :: nike lunar 7.0

  21. Posted June 24, 2014 at 15:18 | Permalink

    Wonderful website you have here but I was curious about if you knew of
    any message boards that cover the same topics discussed here?
    I’d really like to be a part of community where I can get feedback from other
    knowledgeable people that share the same interest. If you
    have any suggestions, please let me know. Thanks a
    lot!

  22. Posted June 26, 2014 at 16:35 | Permalink

    It is not my first time to pay a quick visit this site, i am browsing
    this site dailly and get fastidious data from here every day.

  23. Posted June 28, 2014 at 03:33 | Permalink

    Benefits of designing a Responsive Web Design of your business website.
    ‘ We all know by now that content is king when it comes to internet marketing.
    Interactive Waves always strives to provide
    customer satisfaction through the innovative web design, Web
    Development, Logo Designing and Ecommerce Solutions at an affordable price.

4 Trackbacks

  1. By Nested if-statements tool on January 11, 2010 at 12:14

    [...] You can check it out here [...]

  2. [...] Possibly related posts:A little tool for creating nested if-statements [...]

  3. By So I’m back … | qlikblog.at on February 1, 2011 at 03:10

    [...] 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 [...]

  4. By Set Analysis Wizard « QlikView – My View on March 6, 2012 at 02:37