topic Re: Re: Expression in Chart in QlikView

Expression in Chart

Mon, 23 Dec 2013 08:30:16 GMT

Hi,

I have a expression used in chart.

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(match((AD_ID,>=5004 and <=5019) and (AD_ID,>=5035 and <=5050)),'Prnt Key','TER'))

Could someone tell me what's the error in this expression?

Thanks.

Re: Expression in Chart

tresB — Mon, 23 Dec 2013 08:33:51 GMT

A quick look finds:

AD_ID,>=5004 // remove comma after AD_ID and similarly from AD_ID,>=5035 as well.

Re: Expression in Chart

Mon, 23 Dec 2013 08:39:59 GMT

Hi Tresesco,

Thanks for quick reply.I have tried that before.but still there is some error in the expression.

Re: Expression in Chart

jagan — Mon, 23 Dec 2013 08:42:54 GMT

Hi,

Try this

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(((AD_ID>=5004 and AD_ID<=5019) OR (AD_ID>=5035 and AD_ID<=5050)),'Prnt Key','TER'))

Hope this helps you.

Regards,

Jagan.

Re: Re: Expression in Chart

tresB — Mon, 23 Dec 2013 08:43:22 GMT

One more:

((AD_ID,>=5004 and <=5019) and (AD_ID,>=5035 and <=5050)) - this is logically impossible. May be you want

((AD_ID>=5004 and <=5019) OR (AD_ID>=5035 and <=5050))

Re: Expression in Chart

Mon, 23 Dec 2013 08:55:34 GMT

Hi,

it should be OR.but the expression is still having error after the conditional statement ends,it's throwing error.:(. (AD_ID>=5035 and AD_ID<=5050)),'Prnt Key','TER'))

Re: Re: Expression in Chart

tresB — Mon, 23 Dec 2013 09:00:00 GMT

Add one more ')' at the last and try.

Re: Expression in Chart

Mon, 23 Dec 2013 09:09:25 GMT

Without Match(), i can build the expression but i need match() as there are no distinct values.so two AD_ID's ll be there.so match() is a must.Please understand that.

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(match((AD_ID>=5004 and AD_ID<=5019) OR (AD_ID>=5035 and AD_ID<=5050)),'Prnt Key','TER')))

Above is the latest expression.

Re: Expression in Chart

jagan — Mon, 23 Dec 2013 09:13:06 GMT

Hi,

If you want to use Match() then you have to specify each individual value not >= and <=. Try like this

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(match(AD_ID,5004, 5005, 5006,.... 5019, 5035, 5036, ...........5050), 'Prnt Key','TER')))

Regards,

Jagan.

Re: Re: Expression in Chart

tresB — Mon, 23 Dec 2013 09:14:40 GMT

Could you post your example app with sample data?

Re: Expression in Chart

Mon, 23 Dec 2013 09:24:20 GMT

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(match(5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050),'Prnt Key','TER')))

I can achieve by this.but i want to use and or OR operator as i feel the above may degrade the performance.

any suggestion?

Re: Expression in Chart

Mon, 23 Dec 2013 09:25:56 GMT

Hi Jagan,

Yeah i can achieve with giving all values.that's working out correctly before.

=if(Match(AD_ID,5001,5032),'KEY Cnt',

only(if(match(5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050),'Prnt Key','TER')))

but i want to use operator..any other suggestion to replace this expression?

Re: Re: Expression in Chart

qlikpahadi07 — Mon, 23 Dec 2013 10:02:52 GMT

Hi Gtripathy,

why you need match() only .... if there are no distinct values still using If condition you can achieve that something like

if((AD_ID>=5004 and AD_ID<=5019) OR (AD_ID>=5035 and AD_ID<=5050),'Prnt Key','TER') as ColumnNAME

hope I understood your problem , or you can attach a Sample data with desired output you need

Re: Re: Re: Expression in Chart

Wed, 25 Dec 2013 10:26:23 GMT

Hi,

I have attached a sample data.But in real scenario, there are many ID's so i want to use operator AND/OR to club them.

We need to either use only()and match() and individual ID's or i guess applymap may be does the trick but i'm not sure.

In the detail chart (You ll get it once you click on any AD_Desc), For Parent_Num dimension, i have used that expression.It's not correct.

Note: In real scenario,list of ID are much more..so can't use define them individually like

e.g-

=if(Match(AD_ID,5001),'KEY Cnt',

only(if(match(AD_ID,5004,5005,5006,5007,5008,5009,5010),'Prnt Key','TER')))

This above expression is accurate.But in real scenario, AD_ID dimension contains distinct 30 records around.

Please consider that first.

Thanks.

Re: Re: Re: Re: Expression in Chart

qlikpahadi07 — Thu, 26 Dec 2013 05:01:37 GMT

Hi Gtripathy,

You can achieve by both ways If Else and Match statements. but for Match() you can't give '<=' or '>'... you have to mention all ID but you can create a Variable and make Dynamic Chart for the same.

something like:

=if(Match(AD_ID,$(vVar1)),'KEY Cnt',

if(Match(AD_ID,$(vVar2)),'Prnt Key','TER'))

Please find the Attachment may be helpful .

Re: Re: Re: Re: Expression in Chart

qlikpahadi07 — Thu, 26 Dec 2013 05:03:33 GMT

I don't think this will degrade the performance or you can create a Variable instead.

Re: Expression in Chart

Thu, 26 Dec 2013 18:12:07 GMT

Hi QlikPahadi,

I appreciate your suggestion.

using variable is really handy on these case.though i used AD_ID's like i mentioned in previous expression.using Match().

But if i'm not wrong we can't use if-else.reason is the expression won't work properly when there are two ID's in the chart..:)

thanks for the sample attachment...I appreciate it.

Re: Re: Re: Re: Expression in Chart

qlikpahadi07 — Sun, 29 Dec 2013 04:41:25 GMT

Hi Gtripathy,

It Depends you can still use If Else statements if you have 2 same ID's.

Let say all numbers in Dimension1 say '7' should be 'India' then If(<Dimension1> = 7 , "India', 'Rest') this statement will replace all code '7' to India though it appears twice or thrice

BUT if there are some relation then you can use AND statements like

If(<Dimension1> = 7 and <Dimension2> = 5, "India', 'Rest')

this statement will only replace those code whose Dimension 1 is '7' and Dimension 2 is '5' to India though it appears twice or thrice.

Hope this is helpful

New Year greetings