topic Re: specifc rows formatting in Qlikview in QlikView

specifc rows formatting in Qlikview

vireshkolagimat — Wed, 25 Nov 2020 16:16:04 GMT

hi guys,

I have a table that has 10 rows and first row being the header column. Is there anyway to set different number formatting to different rows.

I need first rows values in integer format and rest in %.

Header 1	Header 2	Header 3
ABC	333	4567
PQR	10.00%	20.00%
LMN	6.99%	2.33%
XYZ	67.00%	12.36%

Regards,

Viresh

Re: specifc rows formatting in Qlikview

Kushal_Chawda — Wed, 29 Jun 2016 09:54:43 GMT

Can you share sample data

Re: specifc rows formatting in Qlikview

sunny_talwar — Wed, 29 Jun 2016 09:55:13 GMT

You can def. do this. This can be based on the dimension itself or RowNo()

If([Header 1] = 'ABC', Num(Expression, '##'), Num(Expression, '##.00%'))

If(RowNo() = 1, Num(Expression, '##'), Num(Expression, '##.00%'))

or some variation of both of these based on your actual data

Re: specifc rows formatting in Qlikview

ciaran_mcgowan — Wed, 29 Jun 2016 10:46:24 GMT

Hi Viresh,

Are there any distinguishing factors in any of the Header 1 names that you can identify as being a number or %? If so, I would create a flag in the script to identify this fact.

if(Header1 = 'ABC', 1, 2) AS Flag.Header1

Then in any of your tables, you can use something like:

if(Flag.Header1 = 1, Num(Header2, '#,##0'), Num(Header2, '0.00%'))

Re: specifc rows formatting in Qlikview

vireshkolagimat — Wed, 29 Jun 2016 11:53:34 GMT

I am using the below expression i am not getting the format.

If(cat = 'ABC', Num([42401], '##'), Num([42401], '##.00%'))

This is the sample data:

Cat	1-Feb	1-Mar
ABC	100	200
PQR	45	89
LMN	77	88

Re: specifc rows formatting in Qlikview

Digvijay_Singh — Wed, 29 Jun 2016 12:00:56 GMT

I think 'c' of Cat should be in upper case.

Re: specifc rows formatting in Qlikview

sunny_talwar — Wed, 29 Jun 2016 12:02:22 GMT

Make sure to select Expression Default formatting on the Number's tab of your chart properties

Re: specifc rows formatting in Qlikview

vireshkolagimat — Wed, 29 Jun 2016 12:07:50 GMT

yeah i corrected it, thanks

Re: specifc rows formatting in Qlikview

vireshkolagimat — Wed, 29 Jun 2016 12:09:29 GMT

I'm getting the below result

ABC 1e+002 2e+002

PQR 4500.00% 8900.00%

Re: specifc rows formatting in Qlikview

sunny_talwar — Wed, 29 Jun 2016 12:17:26 GMT

Try this:

=If(Cat = 'ABC', Num(Value, '##.'), Num(Value/100, '##.00%'))

Re: specifc rows formatting in Qlikview

vireshkolagimat — Thu, 30 Jun 2016 09:57:41 GMT

Hi, thanks it works fine if i don't use the qualify statement. I am using qualify statement to avoid synthetic key and trying the above method but i am not getting the result. Below is the sample expression.

=If(DWE.category=[C F], num(DWE.42460, '##.0'), num( DWE.42490/100, '##.00%'))

Here DWE is the table name

category is the dimension and 42660 and 42490 are dates which are used in expression.

let me know if you need any details.

Regards,

Viresh

Re: specifc rows formatting in Qlikview

sunny_talwar — Thu, 30 Jun 2016 11:19:26 GMT

What is [C F] here? A value within DWE.category? Does it include the square brackets? If it doesn't, try this:

If(DWE.category = 'C F'

if it does, then may be this:

If(DWE.category = '[C F]'

It would be helpful if you can provide some screenshots of your expression and chart where you are using this

Re: specifc rows formatting in Qlikview

vireshkolagimat — Thu, 30 Jun 2016 13:16:09 GMT

Hey thank you. I got it.

Re: specifc rows formatting in Qlikview

sunny_talwar — Thu, 30 Jun 2016 13:26:44 GMT

Great!!

In addition to marking helpful responses, I would request you to close this thread by marking correct answer as well.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny