10 Replies Latest reply: Jul 23, 2012 10:11 AM by Miguel Catalão RSS

Can CASE statement be used in QV?

Kumar Ramanujam

I used to use CASE statement in SQL SELECT statements, what would be the equivalent expression in QV - thanks in advance

  • Re: Can CASE statement be used in QV?
    Jason Michaelides

    I'd recommend ApplyMap().

  • Re: Can CASE statement be used in QV?
    NagaianK

    You may also try

     

    Switch..case..default..end switch

    statement (For details, see the Qlikview Reference manual)

    • Re: Can CASE statement be used in QV?
      Kumar Ramanujam

      Thank you very much for the reply, would you be able to give a example of the Swtich statement in QV - thanks

    • Re: Can CASE statement be used in QV?
      Kumar Ramanujam

      Thank you very much for the reply, would you be able to give a example of the Switch statement in QV - thanks

      • Re: Can CASE statement be used in QV?
        NagaianK

        An extract from the QV Reference Manual giving example of switch statement is:

         

        Examples:

        switch I

        case 1

        load '$(I): CASE 1' as case autogenerate 1;

        case 2

        load '$(I): CASE 2' as case autogenerate 1;

        default

        load '$(I): DEFAULT' as case autogenerate 1;

        end switch

         

        By the way, the manual can be accessed from the help menu. A pdf file of the manual will be in the installation folder of the Qlikview desktop client (C:\Program Files\QlikView\Documentation)

        • Re: Can CASE statement be used in QV?
          Jason Michaelides

          The advantage of ApplyMap() is it's dynamic and control of the mapping can be passed to appropriate business users with a simple Excel document.  For example, let's say your CASE statement is to translate regions into abbreviations, you can set up an Excel doc:

           

          Region | Abbrev

          United Kingdom | UK

          United States of America | USA

          etc | etc

           

          and appropriate users can control the mapping by being granted access to the Excel file.  Then, in your load script:

           

          Map_Regions:

          MAPPING LOAD

               Region

               ,Abbrev

          FROM...ExcelDoc...;

           

          Data:

          LOAD

               Field1

               ,Field2

               ,Field3

               ,ApplyMap('Map_Regions',Field4,'<Unknown>')     AS     RegionAbbrev

          ;

          SQL SELECT....;

           

          Your Field4 is the full region name in your database.

          Now, any changes made by your users would take effect automatically at the next reload without you having to get involved.

           

          Jason

          • Re: Can CASE statement be used in QV?
            Kumar Ramanujam

            Hi Jason, Many thanks for your reply and for your time.

             

            How can I use values from two fields in the mapping, (that is if the value from a field meets a certain criteria then it should pick the value from another field)

             

            In the SQL statement it would look like this:

             

            CASE when WWHS = "IT' then LLOC ELSE WWHS END

            (where WWHS was the warehouse field and if the warehouse was 'IT" then it would be substituted with a value in the Location field)

  • Re: Can CASE statement be used in QV?
    Kumar Ramanujam

    Many thanks for your reply and for your time.

     

    How can I use values from two fields in the mapping, (that is if the value from a field meets a certain criteria then it should pick the value from another field)

     

    In the SQL statement it would look like this:

     

    CASE when WWHS = "IT' then LLOC ELSE WWHS END

    (where WWHS was the warehouse field and if the warehouse was 'IT" then it would be substituted with a value in the Location field)

    • Re: Can CASE statement be used in QV?
      Jason Michaelides

      ApplyMap() is for looking up values from a field and returning mapped values from a different source.  Are you saying that dependent on the value of WWHS you want to return the current row value from another field?  In this case if WWHS='IT' then use the value from LLOC, otherwise leave it as WWHS.  What if WWHS='IT2'.  Would you return a value from another field entirely?  Maybe LLOC2?  If not then a simple IF() statement might be your best shot:

       

      Data:

      LOAD

           Field1

           ,Field2

           ,Field3

           ,IF(WWHS='IT',LLOC,WWHS)     AS     WHSLoc

      ;

      SQL SELECT....;

      • Re: Can CASE statement be used in QV?
        Miguel Catalão

        Hi!

        With mappings, you have the option to define the default value when you apply it:

         

        ApplyMap ('MapName', MapField, 'defaultIfNull');

         

        In the default value, you can add another mapping expression. So if you don't have a match in the first map, you can use the second map. Or you could concatenate 2 mapping tables into one.

         

        If you have more complex stuff with a lot of rules, then the best approach woult be to join the main table the other needed tables with the values you want, to a temp table:

         

        TempTable:

        Select field1, field2, field3 from yourmaintable.

        Left Join

        Load field3, field4  resident maptable1; /*These are not qlikview maps, but normal tables that you will delete in the end of the code.*/

        Left Join

        Load  field3, field5  resident maptable2;

         

         

        Then with a load resident of the temp table, insert your logic to select the

         

        FinalTable:

        Load *, if(isnull(Field4), Field5,Field4) as yourfieldName;

        Load * resident TempTable;

         

        Drop Fields Field5, Field4 from FinalTable;

        Drop tables TempTable, maptable1, maptable2;

         

        Its very important that you use a left Join, in order to mantain the whole data from the temp table.

         

        Hope it helps.