IF(OR(function in Load script

[#1]

Hi,

I need to create a separate column in my load script that I could use to filter out Project data. I have a very heavy data load and will need to create a manual flag in the script. Here is a small piece of my data:

Line of Business	Manufacturer	Entity	GBU Product Target	Product Sub-Class	Manufacturer Type	Standard Cost Source	Quantity	PROJECT SCOPE
OTHER	ADJ	147	GRANT REVENUE	ADJUSTMENTS	UNKNOWN	NOT APPLICABLE	1	EXCLUDED
SERVICES	I118	120	INFECTIOUS DISEASE	OTHER	ALERE	NOT APPLICABLE	2	EXCLUDED
OTHER	ADJ	10	GRANT REVENUE	ADJUSTMENTS	UNKNOWN	NOT APPLICABLE	1	EXCLUDED
OTHER	NOT	73	ROYALTIES	OTHER	NOT RELEVANT	NOT APPLICABLE	0	EXCLUDED
PRODUCTS	I022	31	MALARIA	DIAGNOSTIC TEST	ALERE	TRANSFER PRICE	2	EXCLUDED
PRODUCTS	I022	21	MALARIA	ANTIBODY/ENZYME/REAGENT	ALERE	NOT APPLICABLE	3	EXCLUDED
PRODUCTS	I056	24	HIV	DIAGNOSTIC TEST	ALERE	NOT APPLICABLE	0	INCLUDED

I think I will need two columns here:

Column 1: PROJECT SCOPE with the following logic:

If Line of Business = 'SERVICES" then "EXCLUDED"

     if Manufacturer ="I022" then "EXCLUDED"

     if Entity = '147" then EXCLUDED

     if Manufacturer Type = "UNKNOWN" then "EXCLUDED

     if Manufacturer Type = "NOT RELEVANT" then "EXCLUDED

Everything else should be marked as "INCLUDED"

Column2: COST DATA with the following logic

If PROJECT SCOPE = "INCLUDED" and Standard Cost Source ="ICONNECT" then AVAILABLE

elseif Standard Cost Source ="REPACKAGED" then AVAILABLE

elseif Standard Cost Source ="TRANSFER PRICE" then AVAILABLE

when Standard Cost Source is "NOT APPLICABLE" and Product Sub-Class = "SALES BOM" then "AVAILABLE"

when Standard Cost Source is "NOT APPLICABLE" and Product Sub-Class = "ADJUSTMENTS" then "AVAILABLE"

when Standard Cost Source is "NOT APPLICABLE" and Quantity = 0 then "AVAILABLE"

for everything else it should be "UNAVAILABLE"

I really need help with this as soon as possible!!!!!

Thanks a lot!

[alexandros17]

Column 1:

If (Line of Business = 'SERVICES' or Manufacturer ='I022' or Entity = '147 ' or [Manufacturer Type] = 'UNKNOWN' or Manufacturer Type = 'NOT RELEVANT', 'EXCLUDE', 'INCLUDED' as PROJECT_SCOPE

[#3]

Thank you Alessandro... Should I use INLINE for this?

what function would you use for this?

[alexandros17 · Accepted solution]

Include the script in your load (or select) script:

Load

....

If (Line of Business = 'SERVICES' or Manufacturer ='I022' or Entity = '147 ' or [Manufacturer Type] = 'UNKNOWN' orManufacturer Type = 'NOT RELEVANT', 'EXCLUDE', 'INCLUDED' as PROJECT_SCOPE,

...

From

...

Where

...

Let me know

[#5]

I'm not exactly sure how to write the script statement here... and how many columns to include there

[alexandros17]

What do you mean?

[#7]

I think I got it... i asked a questions before seeing your asnwer. I will just include this in my load script not Inline. Thanks a lot... I will let you know if something didnt work.

[#8]

Alessandro,

When i reload script it always gives me an error

Error in expression:')' expected

Please see my script below:

if([Line of Business]='SERVICES' or [Global Business Unit]='CONSUMER DIAGNOSTICS'or[Global Business Unit]='HEALTH INFORMATION SOLUTIONS' or [Global Business Unit]='CONNECTED HEALTH'or[Entity ID]='147'or [Manufacturer ID]='I154'or [Manufacturer ID]='I088' or[Manufacturer ID]='I015' or [Manufacturer ID]='I022'or[Manufacturer ID]='I034'or [Manufacturer ID]='I124'or [Manufacturer ID]='I115' or [GBU Prd Target]='ROYALTIES'or [GBU Prd Target]='FREIGHT' or [Manufacturer Type] ='NOT RELEVANT' or [Manufacturer Type] ='UNKNOWN','EXCLUDED SEGMENT','INCLUDED PRODUCTS') as [PROJECT_SCOPE] 

[vgutkovsky]

Try to make sure you have a space before and after "or" (so that QlikView knows it's a keyword and not part of the next or previous word).

Vlad

[#10]

Vlad,

Thanks a lot. It was exactly the case!