topic Re: Grouping a range in an IF statement in QlikView

Grouping a range in an IF statement

Tue, 23 Sep 2014 18:13:51 GMT

Is there a way to group a range within an IF statement without having to list each item on its own line? I have commodity codes in a range of 5201 thru 5251 and I need to group them into T-SLEEVES. I know I can do this individually but I would rather do this in less than 50 lines if possible. Please advise.

Re: Grouping a range in an IF statement

ecolomer — Tue, 23 Sep 2014 18:38:50 GMT

The codes are numeric and are at equal intervals?

Re: Grouping a range in an IF statement

Tue, 23 Sep 2014 20:55:16 GMT

Here is a sample of so code that already works:

If(WildMatch([CommodityCode], '*5002*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '*5004*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '*5005*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '*5006*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '*5007*'), 'D-SLEEVES', etc...

So since I need to use single quotes around the commodity code '5002', does that mean there are text and not numeric?

Re: Grouping a range in an IF statement

Tue, 23 Sep 2014 21:04:42 GMT

I think QV would treat them as numeric.

Can you use a wildcard character around '50**' for different intervals?

Re: Grouping a range in an IF statement

Tue, 23 Sep 2014 21:14:58 GMT

I can use wildcard characters but I am not sure what you mean for different intervals. I do not want to type 50 lines of "IF" statement to assign this group to T-SLEEVES (see example above). Since these values are concurrent (one after another), is there a way I can do this assignment, in the script, in less lines?

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 12:50:32 GMT

Maybe like

If(WildMatch([CommodityCode], '500*'), 'D-SLEEVES',

f(WildMatch([CommodityCode], '501*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '502*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '503*'), 'D-SLEEVES',

If(WildMatch([CommodityCode], '504*'), 'D-SLEEVES',

and so on. Maybe not the most elegant solution but saves you a lot of lines.

Re: Grouping a range in an IF statement

anbu1984 — Wed, 24 Sep 2014 12:54:39 GMT

=If([CommodityCode]>= 5201 And [CommodityCode] <= 5251, 'T-SLEEVES')

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 13:58:21 GMT

Or use a list of codes and sleeves (INLINE or an Excel sheet) in combination with ApplyMap

Code_Sleeves:

MAPPING INLINE [

CommunityCodeShort, Sleeves

5002, D-SLEEVES

5004, D-SLEEVES

5005, D-SLEEVES

5006, D-SLEEVES

5007, D-SLEEVES

];

ApplyMap('Code_Sleeves',Left([CommunityCode],4),'Nothing Found')

Or something like that

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 17:09:16 GMT

I will give this a try.

Thank You!

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 17:10:40 GMT

This would not work because it will bring in commodities that are NOT in the same group.

Thanks you.

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 17:11:28 GMT

I will keep this in mind.

Thank you.

Re: Grouping a range in an IF statement

Wed, 24 Sep 2014 18:25:40 GMT

When I try to use this code, the script bombs. Is it because I have IF statements that are using the WILDMATCH and I can not mix the two?

Re: Grouping a range in an IF statement

MarcoWedel — Wed, 24 Sep 2014 22:45:26 GMT

Hi,

your usage of

WildMatch([CommodityCode], '*5002*'

sugests, that there might be more than just those 4 numerical characters contained in your CommodityCode field.

Do you have sample data to clarify?

thanks

regards

Marco