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.