Solved: Expression to rename column names - Qlik Community

Report Inappropriate Content · ‎2013-12-18

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.

swuehl · ‎2013-12-19

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')))

View solution in original post

swuehl · ‎2013-12-18

Can't reproduce your issue, works for me.

LOAD

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')) as AD_ID_TEXT, AD_ID INLINE [

AD_ID

1001

1032

1002

1003

1033

1034

1021

1022

1025

1030

1031

1051

1052

2000

];

Report Inappropriate Content · ‎2013-12-18

Hi Swuehl,

Expression i build seems correct but i don't know what's going wrong.

as per the above expression if (AD_ID>=1021 and AD_ID<=1031)or AD_ID>=1051, it should show 'KEY TER', but when AD_ID turns 1027 it's showing TERR which is correct.but when it's 1021,1023,1022 till 1026 it's showing 'KEY TER' but for 1027 it's correct.again for 1028 it's incorrect and showing as KEY TER.

Thanks.

swuehl · ‎2013-12-18

As I said, I can't reproduce your issue.

You'll need to post a sample that demonstrates your issue.

er_mohit · ‎2013-12-18

try this

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

if( (AD_ID>=1021 and AD_ID<=1031) or AD_ID=1002 or AD_ID=1003 or AD_ID=1033

or AD_ID=1034 or AD_ID>=1051,'KEY TER','Prnt TER'))

Report Inappropriate Content · ‎2013-12-18

I tried to simplify it in other way around with the below expression.

=if(AD_ID=1001 or AD_ID=1032,'KEYONE Count',if((AD_ID>=1005 and AD_ID<=1019) or (AD_ID>=1035 and AD_ID<=1050) ,'Prnt TER','KEY TER'))

now ID 1007.. coming in the wrong way

swuehl · ‎2013-12-18

Please double check that all your AD_ID values are numeric and not mixed with textual values.

If this doesn't help, please post a small sample or at least a sample of your AD_ID values.

Report Inappropriate Content · ‎2013-12-18

I traced the issue..When there is two AD_ID it's not working out properly.AD_ID is Numeric only.like 1001,1002....

Any suggestion?

swuehl · ‎2013-12-18

I see, you are using this in a chart expression, where AD_ID is not a dimension and you have more than 1 possible values for AD_ID.

What do you expect to get when there are multiple options? A concatenation?

=concat

if(AD_ID=1001 or AD_ID=1032,'KEYONE Count',if((AD_ID>=1005 and AD_ID<=1019) or (AD_ID>=1035 and AD_ID<=1050) ,'Prnt TER','KEY TER'))

,', ')

Report Inappropriate Content · ‎2013-12-18

Hi,

yes I'm using in Chart expression.But AD_ID is a dimension here.And another dimension is Parent_Num where i'm using this expression.

I ll try possibly to post a sample at the earliest.

This is a straight table.where 6 dimensions are used.AD_ID and Parent_Num are two of them.

Based on AD_Desc, this straight table populates.requirement is that Parent_Num should display differently for differernt AD_ID's.

Note:everytime we click on any AD_Desc ,it ll populate detailed straight table.Where AD_ID,Parent_Num and other fields will be there.

For each AD_Desc,only maximum two AD_ID ll display in the table

Matthew Riedl