Expression in Chart

[#1]

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.

[#2]

=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?

[#3]

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?

[qlikpahadi07]

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

[#5]

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.

[qlikpahadi07 · Accepted solution]

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 .

[qlikpahadi07]

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

[#8]

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.

[qlikpahadi07]

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