topic OR in QlikView

OR

Wed, 20 Jan 2010 15:20:44 GMT

I need to run a formular taken from Excel. This formular uses the OR function within the IF function to keep the formular short. Qulickview, however, does not support the OR fuinction.

Dose anyone know the equiverlant function within Qlickview? [:D]

OR

Wed, 20 Jan 2010 15:37:01 GMT

futher more I have relaised that Qlickview can only contain 3 conditions within the IF. I need more how do I get around this

OR

Wed, 20 Jan 2010 15:50:13 GMT

Hello Symon

The following expression uses the OR statement (syntax is different to excel, but principles are the same):

=Sum(IF(Employee='A.Jones' OR Employee='F.Perry',1,0))

Not too sure what you mean by "Only 3 conditions", I have an expression working as follows:

=GetFieldSelections("YYY_FinancialData_Value") & ' by ' &
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_ClassName','Investment Class Name',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_ClassType','Investment Class Type',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Country','Investment Country',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Currency','Investment Currency',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_GeographicLocation','Investment Geographic Location',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_LegalName','Investment Legal Name',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Region','Investment Region',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_ShortName','Investment Short Name',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_StartDate','Investment Start Date',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Ticker','Investment Ticker',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_TradingCcy','Investment Trading Currency',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'RG_InvestmentType','RL Investment Type',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Type','Investment Type',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'IT_Vintage','Investment Vintage',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'RG_Investment','Investment Name',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'RG_Ownership','Ownership',
 IF(GetCurrentField("XXX_CycleGroup_Investment") = 'PS_PurchaseYear','Purchase Year')))))))))))))))))

But maybe this is not what you're after, if it relates to conditions within a single IF statement then I haven't come across the problem yet, but there's always ways around the problem, you just need to be a bit more specific about the issue.

OR

Wed, 20 Jan 2010 17:15:30 GMT

Thanks Nigle

The OR works perfectly

As for the conditions. I am trying to get the following formular to work:

SUM(IF(major_group='OTH' OR LEN(ProductManager)>1 OR major_group ='WAM' OR LEFT(item_no,3)='SNB' OR LEFT(order_no,1)='A' OR promo_text2 ='CREDIT REBILL',0,IF(invoice_no,1)='D',sell_price*0.005,IF(major_group) ='PRO' OR (LEN(promo_text&promo_text2)>1 OR len(opg_bundleid)>1,(margin+(frontend_addback+backend_addback+customer_rebate+ProductManager),0))))AS TEST

All the occurse is an error message stating that: [:'(]

"Error in expression:
If takes 2-3 parameters"

OR

Wed, 20 Jan 2010 17:27:52 GMT

Couple of things there:

1. Is this a formula in your load script, or is it a formula in a chart/table?

2. If this is in a chart or table, I don't understand what the AS TEST part is at the end.

3. I think you have an issue in the middle of the formula where you have IF(Invoice_no,1), I'm guessing this should read IF(LEFT(Invoice_no,0))

4. There is also a problem in the second IF(MajorGroup because you are immediately closing the bracket on that

5. Your final result looks weird too, it appears that you might have:

(Margin+(Frontend_addback+backend_addback+customer_rebate+ProductManager),0)

In this part, what is the ,0 supposed to be doing, I think you might have a bracket in the wrong place

See following:

SUM
 (
 IF
 (
 major_group='OTH'
 OR LEN(ProductManager)>1
 OR major_group ='WAM'
 OR LEFT(item_no,3)='SNB'
 OR LEFT(order_no,1)='A'
 OR promo_text2 ='CREDIT REBILL'
 ,0
 ,IF(invoice_no,1)='D' //First problem is here
 ,sell_price*0.005
 ,IF(
 major_group) ='PRO' //Second problem is here, should not be closing the bracket
 OR (LEN(promo_text&promo_text2)>1
 OR len(opg_bundleid)>1
 ,(margin+(frontend_addback+backend_addback+customer_rebate+ProductManager),0) //Next problem is here, assume the ,0 should be the ELSE part
 )
 )
 )AS TEST //Not sure about the AS TEST

OR

Wed, 20 Jan 2010 18:25:40 GMT

Hi Neil.

Thanks for the help

By the way this is in my script

I have done tyour corrections (thanks for them an all)

Still comming up with the same error message

SUM(IF(major_group='OTH'

OR LEN(ProductManager)>1

OR major_group ='WAM'

OR LEFT(item_no,3)='SNB'

OR LEFT(order_no,1)='A'

OR promo_text2 ='CREDIT REBILL',0

,IF(left(invoice_no,1)='D',sell_price*0.005

,IF(major_group ='PRO'

OR LEN(promo_text&promo_text2)>1

OR LEN(opg_bundleid)>1

,(margin+(frontend_addback+backend_addback+customer_rebate+ProductManager),([Sales Margin]))))))AS TEST,

OR

Thu, 21 Jan 2010 13:20:35 GMT

Hi all.

I have cracked it. Thanks to Neil

The key seemed to be to move the parts of the IF which created the qualifiers upon which the conditions worked

So instead of having =if(len(PM)>1 I move the Len(PM) into the load statment renamed it (condition1) and had IF(condition1>1)

This means relaoding the entier formula in the load statment at the top (the one with the magic *) but this was relaitivley easy

load
*,
(MTD*WKD)/WDP as PrjSales,

IF(promo_text2='Credit Rebill',0,IF(condition3 ='SMB',0,IF(Condition1='A',sell_price*0.005,IF(salesperson='BARRY B',0,if(major_group='OTH' or 'WAM',0,if((Condition4)>0,margin,[Sales Margin])))))) as Trial,

( MTDADJ*WKD)/WDP as PrjMargin;

LOAD [RD-ID],
dim_invoice_key,
cono,
warehouse,
invoice_no,
ConsolidatedInvoice,
DATE(invoice_date,'YYYY-MM-DD') AS Truedate,
left(order_no,1) as Condition2,
order_line,
left(item_no,3) as condition3,
left(item_no,6) as Product,
source,
customer,
customer_class,
major_group,
minor_group,
item_div,
despatch_no,
direct_del_flag,
quantity,
sell_price,
cost,
margin,
[Sales Margin],
pref_supplier,
product_group,
promo_text,
opg_deal_version,
OPG_price,
OPG_percent,
OPGDeal_FrontEnd,
opg_supplier,
opg_comment,
opg_enduser,
opg_bundleid,
M_S,
reason_code,
credit_reason,
vat_number,
customer_name,
unit_price,
unit_cost,
salesperson,
team,
overgroup,
account_owners,
promo_text2,
backend_addback,
customer_rebate,
customer_rebate_provision,
pereferred_partner,
customer_order_ref,
gl_flag,
base_cost,
list_price,
updated,
base_list_price,
frontend_addback,
region_code,
period,
prod_manager,
type,
DD_PO_NO,
DD_PO_Line,
ProductManager,
line_value_prime,
vat_value_prime,
vat_code,
vat_rate,
currency_code,
mrupdated,
date_created,
Len(opg_bundleid&promo_text&promo_text2) as Condition4,
left(invoice_no,1) as Condition1,

OR

Tue, 11 May 2010 00:24:46 GMT

you could use mixmatch function, it much better when working with multiples conditions.