topic Re: Help with if Load Script Statement in QlikView

Help with if Load Script Statement

jcampbell474 — Wed, 06 Apr 2016 18:29:44 GMT

I need to implement this in the preceding load to create a column.

if(mID=50 OR Manual=10 OR Match(cy_nbr=('9999999','8888888')) AND Not Match (Product,'MM','NN'), 'Auto')

But I'm having trouble getting it to work. I think it's the Not Match statement. Can anyone steer me in the right direction?

Thanks in advance!

Re: Help with if Load Script Statement

sunny_talwar — Wed, 06 Apr 2016 18:32:22 GMT

May be this:

If((mID=50 or Manual=10 or Match(cy_nbr, '9999999','8888888')) and not Match(Product, 'MM', 'NN'), 'Auto')

Re: Help with if Load Script Statement

swuehl — Wed, 06 Apr 2016 18:32:52 GMT

I think it's your first match:

Match(cy_nbr,'9999999','8888888')

Then check if you need to embed part of your boolean expressions into parentheses to get your correct logic.

Re: Help with if Load Script Statement

jcampbell474 — Thu, 07 Apr 2016 12:27:50 GMT

Thank you both for the quick replies!

swuehl, I completely understand the first Match issue. Thank you for pointing it out.

Sunny, I think your statement will work, but QV doesn't like the comma before 'Auto'. I tried rearranging some of the conditions, but can't get it right. Should it be some type of nested if, instead?

Re: Help with if Load Script Statement

sunny_talwar — Thu, 07 Apr 2016 12:33:29 GMT

I don't see any issue with the expression. Are you seeing red lines in expression editor or the result is not as you would like?

Re: Help with if Load Script Statement

jcampbell474 — Thu, 07 Apr 2016 12:43:23 GMT

Yes, exactly. Here is a screenshot:

Re: Help with if Load Script Statement

sunny_talwar — Thu, 07 Apr 2016 12:47:08 GMT

Would you be able to share few lines or data with your expected output?

Re: Help with if Load Script Statement

jcampbell474 — Thu, 07 Apr 2016 13:07:42 GMT

I think I see why more code would be more helpful. I'm actually cleaning up some SQL and going to implement a preceding load. Here is the case statement (field names and values changed, of course):

WHEN (irmD=55 OR eMan=99 OR y_nbr IN('9003682','9004164')) AND Pr NOT IN('HH','DF') THEN 'Auto'

WHEN ((vst='AA' AND (eMan=99 OR y_nbr IN ('9003682','9004164'))) AND Pr IN ('HH','DF')) THEN 'HO'

WHEN ((irmD=60 OR eMan=92) AND Pr IN('HH','DF')) OR

(irmD=61 and Pr in ('HH', 'DF') AND vst = 'LA') OR

(irmD=16 AND Pr IN('HH','DF') AND vst = 'TX') THEN 'Imp'

WHEN (irmD=60 OR eMan=99) AND Pr='Prs' THEN 'mp'

WHEN irmD=20 AND vst='AA' AND SubPr='Prs' THEN 'Val'

WHEN irmD=20 AND vst='AA' AND SubPr='Srs' THEN 'mit'

WHEN irmD=20 AND vst='AA' AND SubPr='Prs' THEN 'CAV'

WHEN irmD IN(33,55) OR eMan IN(1,2,3,4,5,6,7,8) THEN 'Pref'

WHEN ite = 'HH' AND Pr = 'HH' and SubPr = 'MomeHH' THEN 'Mome'

ELSE 'adit'

END

AS 'ment'

Thanks!

Re: Help with if Load Script Statement

jcampbell474 — Thu, 07 Apr 2016 15:31:21 GMT

SunnyT,

Nevermind, I was just pasting it into the script...and didn't have the Load statement. Caused QV to flag it (just the comma) as incorrect Rookie mistake. Your initial reply is correct and marked as such.

My apologies for any time that was wasted. And a tremendous thanks for the help!