4 Replies Latest reply: Jul 12, 2017 12:45 PM by Vinay Maidargi RSS

    Alternative to CASE when in QlikView

    Vinay Maidargi

      I have the below code in case when statement which are in sql server . I need to implement the same in qlikview. Please let me know how can I write case when statements in qlikview.

      Below is the code

       

      , u.[Attendee Violation Y/N]      =

       

             CASE   WHEN  u.[Expense Type] in ('Meals & Drinks', 'Entertainment', 'Business Meals & Drinks Covered Recipient', 'Per diem - meals', 'Business Meals & Drinks')

       

                           AND u.[Attendee Type] in ('Covered Recipient','US Business Guest')

       

                    THEN CASE     WHEN a.city is NOT NULL and a.state is NOT NULL and CAST(replace(u.[Amount Attendee (Reimbursement Currency)],',','') as float) > 110 THEN '110 Violation' -- Comparing with high cost cities for violation

       

                                 WHEN a.city is NULL and a.state is NULL and CAST(replace(u.[Amount Attendee (Reimbursement Currency)],',','') as float) > 90 THEN '90 Violation'

       

                                 ELSE 'No Violation'

       

                                 END

       

                    ELSE 'No Violation'

                      END

       

      FROM fact_table u

             LEFT JOIN high_cost_geographies a     ON u.[City Location] = a.city AND u.[State Province Region] = a.state

       

       

      I tried to use If Then Else, but then giving the error as IF takes 2-3 parameters. Not sure where I am going wrong.

       

      Is there any other alternative?

      Please help.

      Thanks,

      Vinay

       

      PS: I am a newbie to Qlik

        • Re: Alternative to CASE when in QlikView
          Ruben Marin

          Hi Vinay, it can be something like:

          If(Match([Expense Type], 'Meals & Drinks', 'Entertainment', 'Business Meals & Drinks Covered Recipient', 'Per diem - meals', 'Business Meals & Drinks') AND Match([Attendee Type], 'Covered Recipient','US Business Guest')

          , If(not isnull(city) and not isnull(state) and [Amount Attendee (Reimbursement Currency)]>100

             , '110 Violation' // Comparing with high cost cities for violation

             , If(not isnull(city) and not isnull(state) and [Amount Attendee (Reimbursement Currency)]>90,

               , '90 Violation'

               , 'No Violation')

          , 'No Violation')


          That error message seems related to a lost comma or parenthesys, causing bad syntax.

          • Re: Alternative to CASE when in QlikView
            Peter Cammaert

            Although I think it's a bad idea to try to translate SQL to QlikView Script in a 1:1 fashion, you should try again but this time, try to use the IF() function instead of the IF THEN ELSE control statement. You cannot nest control statements inside LOAD statements in QlikView Script. But you can use as many IF() function calls in a single LOAD statement as you want. See here: if ‒ QlikView

             

            Ruben posted a good example of the use of the IF() function in your situation. On the other hand, trying to translate SQL code into a QlikView Script equivalent form may make your statement unreadable and will surely make it awfully difficult to maintain. If possible, try to create a LOAD statement based on the business logic behind this construct.