Alternative to CASE when in QlikView

[vinaymaidargi]

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

[rubenmarin]

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.

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

[vinaymaidargi]

Thanks Ruben, This worked quite well. Instead of putting everything in one if statement I split them into two . Yes that match function was very helpful. Thanks for the solution. Cheers

[vinaymaidargi]

Thanks Peter. Well yes I do agree that translating SQL to Qlikview straight forward would be bad. Probably understand the logic behind the SQL, and implement as per the Qlik standard functionality.

Ruben's post was very helpful. So it worked very well.

Thanks for your suggestions/comments. Well appreciate the same. Cheers