topic Re: IF(OR(function in Load script in QlikView

IF(OR(function in Load script

Mon, 08 Sep 2014 14:58:20 GMT

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!

Re: IF(OR(function in Load script

alexandros17 — Mon, 08 Sep 2014 15:05:10 GMT

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

Re: IF(OR(function in Load script

Mon, 08 Sep 2014 15:25:38 GMT

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

what function would you use for this?

Re: IF(OR(function in Load script

alexandros17 — Mon, 08 Sep 2014 15:32:15 GMT

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

Re: IF(OR(function in Load script

Mon, 08 Sep 2014 15:33:28 GMT

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

Re: IF(OR(function in Load script

alexandros17 — Mon, 08 Sep 2014 15:35:30 GMT

What do you mean?

Re: IF(OR(function in Load script

Mon, 08 Sep 2014 15:40:55 GMT

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.

Re: IF(OR(function in Load script

Mon, 08 Sep 2014 17:16:16 GMT

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]

Re: IF(OR(function in Load script

vgutkovsky — Mon, 08 Sep 2014 17:21:27 GMT

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

Re: IF(OR(function in Load script

Mon, 08 Sep 2014 17:28:25 GMT

Vlad,

Thanks a lot. It was exactly the case!

Re: IF(OR(function in Load script

vgutkovsky — Mon, 08 Sep 2014 17:34:49 GMT

No problem. Alessandro's answer should be marked as correct. Mine could be helpful 🙂

Thanks,

Vlad