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