topic Re: Expression to rename column names in QlikView

Expression to rename column names

Wed, 18 Dec 2013 09:09:25 GMT

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.

Re: Expression to rename column names

swuehl — Wed, 18 Dec 2013 09:22:22 GMT

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

];

Re: Expression to rename column names

Wed, 18 Dec 2013 10:02:00 GMT

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.

Re: Expression to rename column names

swuehl — Wed, 18 Dec 2013 10:08:07 GMT

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

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

Re: Expression to rename column names

er_mohit — Wed, 18 Dec 2013 10:16:39 GMT

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

Re: Expression to rename column names

Wed, 18 Dec 2013 11:09:26 GMT

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

Re: Expression to rename column names

swuehl — Wed, 18 Dec 2013 11:12:17 GMT

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.

Re: Expression to rename column names

Wed, 18 Dec 2013 12:10:12 GMT

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?

Re: Expression to rename column names

swuehl — Wed, 18 Dec 2013 12:13:27 GMT

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

,', ')

Re: Expression to rename column names

Wed, 18 Dec 2013 12:36:08 GMT

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

Re: Expression to rename column names

Wed, 18 Dec 2013 20:12:37 GMT

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.

Re: Re: Expression to rename column names

swuehl — Wed, 18 Dec 2013 20:38:00 GMT

Maybe like this?

Re: Expression to rename column names

jerem1234 — Wed, 18 Dec 2013 20:57:08 GMT

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!

Re: Expression to rename column names

Thu, 19 Dec 2013 05:07:02 GMT

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.

Re: Expression to rename column names

Thu, 19 Dec 2013 05:19:10 GMT

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

Re: Re: Expression to rename column names

swuehl — Thu, 19 Dec 2013 07:14:42 GMT

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

Re: Expression to rename column names

Thu, 19 Dec 2013 08:47:30 GMT

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.

Re: Expression to rename column names

swuehl — Thu, 19 Dec 2013 11:59:55 GMT

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

)

=concat(

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

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

)

,', ')

Re: Expression to rename column names

Thu, 19 Dec 2013 12:14:52 GMT

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.