Expression to rename column names

[#1]

Hi,

I build one expression with if statement to rename the column names based on set of records.

=if(AD_ID=1001 or AD_ID=1032,'KEYONE Count',

if(AD_ID=1002 or AD_ID=1003 or AD_ID=1033

or AD_ID=1034 or (AD_ID>=1021 and AD_ID<=1031) or AD_ID>=1051,'KEY TER','Prnt TER'))

it's working fine for all the ID's except for the bold section of the expression.When AD_ID>= 1021 and <=1031 the value should display as KEY TER but it's showing as Prnt TER.

Any suggestion ?

Thanks in advance.

[#2]

Hi,

Attached a sample app.

The requirement is

for AD_ID=5007,5001,5008,5007,5009 Parent_Num column in the "Detail record of A_Available" (Once you click on any AD_Desc, it ll populate detail straight table) should display as KEY_TER

for ID=5003,5004,5005,5006,5010,5011 it should display as Prnt_KEY.For rest others KEY_DTL..

Please note: In real scenarios the ID's will be more so please suggest simplified expression.I have tried with

nested IF statement but result already mentioned.

Thanks in advance.

[swuehl]

Maybe like this?

[jerem1234]

Are you wanting to make the values for your field Parent_Num to change? Or are you trying to change the label for that column?

If you are trying to change the label for that column, then you can put this formula into the label for your dimension:

=if(Match(min(AD_ID), 5007, 5001, 5008, 5009), 'KEY_TER', if(Match(min(AD_ID), 5003, 5004, 5005, 5006, 5010, 5011), 'Prnt_KEY', 'KEY_DTL'))

Note that since you are trying to change the column label, you can't just use AD_ID. This is because when you have multiple values for AD_ID, it won't know which one to use. If all of your AD_ID's are compatible in your chart, just use the min() function to only return one of those. Then you can match them with your if statement.

Hope this helps!

[#5]

Hi Jerem,

I want to change the Label only.Yes as i mentioned earlier, AD_ID ll have two different ID's like 5001,50011..So when i tried if condition,it didn't work out for few ID's because for that table two ID's were there.

there are many ID's and i have to use AND,OR condition anyway.

e.g- AD_ID=5007,5001,5008,5007,5009 The column name=Key TER

for ID=5003,5004,5005,5006,5010,5011 Column name should be labelled as Prnt Ter

and for rest others TER..

This is sample app.but in real the list of ID is much more.

Thanks.

[#6]

Hi swuehl,

We have to rename the label of Parent_Num column based on set of ID's.and the expression you have used seems taking value KEY DTL for 5007 and 5009 but expression says it should be KEY TER. Which is the issue i'm facing.

if(match(AD_ID,5007,5001,5008,5007,5009),'KEY_TER'

, if(match(AD_ID,5003,5004,5005,5006,5010,5011),'Prnt_KEY'

,'KEY_DTL'))

[swuehl · Accepted solution]

Sorry, I have misunderstood what you want. Ok, you want to set the expression label using an expression.

For a detail chart with different AD_ID, and IDs belonging to different categories, you'll need to define which category to chose. If the distinct IDs belong to the same category, as for 5007 and 5009, you can use only() function:

=only(if(match(AD_ID,5007,5001,5008,5007,5009),'KEY_TER'

, if(match(AD_ID,5003,5004,5005,5006,5010,5011),'Prnt_KEY'

,'KEY_DTL')))

[#8]

Thanks a lot Swuehl,

Seems like only thing helped me out is only()..:)

=if(Match(AD_ID,5001,5002),'KEY TER',

only(if(match(AD_ID,(>=5005 and <=1050)),'Prnt Key','Key_DTL')))

Could you please tell me what's wrong in the above expression?

Thanks again.

Cheers.

[swuehl]

As soon as AD_ID has more than one possible values, your first match condition can't be evaluated correctly.

You'll need to embed your if-statements into an aggregation, only() or some other like minstring() / maxstring() /concat():

=only(

if(Match(AD_ID,5001,5002),'KEY TER',

     if( AD_ID >=5005 and AD_ID <=5050,'Prnt Key','Key_DTL')

     )

)

or

=concat(

if(Match(AD_ID,5001,5002),'KEY TER',

     if( AD_ID >=5005 and AD_ID <=5050,'Prnt Key','Key_DTL')

     )

,', ')

[#10]

Hi,

=only(if(match(AD_ID,5007,5001,5008,5007,5009),'KEY_TER'

, if(match(AD_ID,5003,5004,5005,5006,5010,5011),'Prnt_KEY'

,'KEY_DTL')))   is the only expression working correctly as my chart have two Distinct ID.But since the AD_ID are much more.I wanted to use >= or <= operator.

This below expression is exactly what i need..

=only(if(Match(AD_ID,5001,5032),'KEY TERR',

if(Match(AD_ID>=5004 and AD_ID<=5019) or (AD_ID>=5035 and AD_ID<=5050)),'Prnt Key','Key_DTL')))

Thanks.