topic Re: Variable Tutorial and basic overview in QlikView

Variable Tutorial and basic overview

Fri, 08 May 2015 15:53:14 GMT

Hi All,

I am looking for a resource that will serve as a foundation to understanding variables in Qlikview, when to use them, when to use Set vs Let, and how to use in formulas. I have found the below, but I was wondering if there is a better resource.

In my own script I am trying to create a variable that will store certain states that if shipped from a certain DC, would be classified as "over fence" shipments

My attempts that are failing:

Set vDC1OverFenceState= 'CA;OR;WA;ID;NV;AZ;AK;HI;UT;MT;WY;CO;NM;TX';

Set vDC2OverFenceState= 'ND;SD;NE;KS;OK;MN;IA;MO;AR;LA;WI;IL;KY;TN;MS;MI;IN;AL;OH;ME;NH;VT;MA;RI;CT;NY;PA;NJ;DL;WV;VA;FL;GA;SC;NC;MD;PR;VI';

IF(WH='DC1' and [SHIP TO STATE]= $(vDC1OverFenceState),1,0) as N7OverFence,

IF(WH='DC2' and [SHIP TO STATE]= $(vDC2OverFenceState),1,0) as L7OverFence,

I am thinking I need to use peek somehow, but something is missing in my logic. Any assistance appreciated.

Qlikview: Variables, SET, LET and Dollar Sign Expansion | Hyunku's Blog

Re: Variable Tutorial and basic overview

sunny_talwar — Fri, 08 May 2015 16:08:35 GMT

Try this may be:

IF(WH='DC1' and Match([SHIP TO STATE], $(vDC1OverFenceState)),1,0) as N7OverFence,

IF(WH='DC2' and Match([SHIP TO STATE], $(vDC2OverFenceState)),1,0) as L7OverFence,

Not entirely sure if the set or let will work. Try the above with both let and set.

HTH

Best,

Sunny

Re: Variable Tutorial and basic overview

marcus_sommer — Fri, 08 May 2015 16:14:23 GMT

Very good explanations will you find here:

The Magic of Variables

The Magic of Dollar Expansions

- Marcus

Re: Variable Tutorial and basic overview

Anonymous — Fri, 08 May 2015 16:15:20 GMT

Joe

Have a look at these 3 blog posts by hic‌. His blog posts are always good.

The Magic of Variables

The Magic of Dollar Expansions

The Little Equals Sign

Re: Variable Tutorial and basic overview

Fri, 08 May 2015 17:18:03 GMT

Thank you all!

Re: Variable Tutorial and basic overview

Fri, 08 May 2015 18:49:11 GMT

Thanks Bill, Nice links

Re: Variable Tutorial and basic overview

Fri, 08 May 2015 20:49:08 GMT

If anyone is interested, here is how I got it to work. Thanks again for the excellent links.

SET vDC1OverFenceState= 'CA','OR','WA','ID','NV','AZ','AK','HI','UT','MT','WY','CO','NM','TX';

SET vDC2OverFenceState= 'ND','SD','NE','KS','OK','MN','IA','MO','AR','LA','WI','IL','KY','TN','MS','MI','IN','AL','OH','ME','NH','VT','MA','RI','CT','NY','PA','NJ','DL','WV','VA','FL','GA','SC','NC','MD','PR','VI';

IF(WH='DC1' and Match([SHIP TO STATE],$(vDC1OverFenceState)),1,0) as N7OverFence,

IF(WH='DC2' and Match([SHIP TO STATE],$(vDC2OverFenceState)),1,0) as L7OverFence,

Re: Variable Tutorial and basic overview

sunny_talwar — Fri, 08 May 2015 21:34:59 GMT

That is exactly what I suggested . You might have overlooked it.... But I am glad you got your solution...

Re: Variable Tutorial and basic overview

Sat, 09 May 2015 15:31:45 GMT

Yes thank you for the input! Still getting used to how and where to reply!

Re: Variable Tutorial and basic overview

sunny_talwar — Sat, 09 May 2015 15:32:48 GMT

Hahahaha not a problem my friend