topic Re: Change row value in QlikView

Change row value

Tue, 31 Jan 2017 06:59:17 GMT

IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2, LEVEL1) AS LEVEL4

When I load this line, it will output in a pivot table showing 16.3 because in my excel sheet that I load from, 16.30 is shown as 16.3(For I don't know what reason. My company say its like that).

What I would like to do is to change the 16.3 showing in the pivot table to '16.30'. Is there any way I can do it like for example

IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2 AS '16.30', LEVEL1) AS LEVEL4?

The above code does not work btw. Thank very much

Re: Change row value

Anil_Babu_Samineni — Tue, 31 Jan 2017 07:10:15 GMT

Can you provide sample data and expected output to see

atleast, 10 rows we are expecting

Re: Change row value

stabben23 — Tue, 31 Jan 2017 07:20:02 GMT

Try use the num()

IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), num(LEVEL2,'##,##'), LEVEL1) AS LEVEL4

Re: Change row value

sdmech81 — Tue, 31 Jan 2017 07:27:45 GMT

Try like this,Go to number tab of pivote chart and restrict to 2 decimals

Re: Change row value

sdmech81 — Tue, 31 Jan 2017 07:28:56 GMT

Try this it should work!

Re: Change row value

Tue, 31 Jan 2017 07:29:58 GMT

Currently when I use this code, IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2, LEVEL1) AS LEVEL4, I will receive a column of

LEVEL4

16.3

This is because my excel data treats 16.30 as 16.3 and therefore, loaded it as shown.

In reality, the excel data that is 16.3 should be shown as 16.30 on my qlikview. So I would like to know if there is a way to manually load 16.3 but display it as 16.30. So the expected output on qlikview will be

LEVEL4

16.30

Re: Change row value

Tue, 31 Jan 2017 07:31:25 GMT

Sorry tried but it doest work

Re: Change row value

stabben23 — Tue, 31 Jan 2017 07:32:00 GMT

I'll think he use this as Dimension value, not expression

Re: Change row value

Tue, 31 Jan 2017 07:33:35 GMT

Sorry but your method is to adjust the cell values decimal points. I would like to change the way my dimensions are being displayed. something like

IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2 AS '16.30', LEVEL1) AS LEVEL4

I would like to manually load that specific LEVEL2 Dimension as '16.30'

Re: Change row value

stabben23 — Tue, 31 Jan 2017 07:36:47 GMT

Does you LEVEL2 Field even consist 16.30?

Show us the value in Level2 from Excel without any modifications.

Re: Change row value

sdmech81 — Tue, 31 Jan 2017 07:38:18 GMT

try

num(IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2 AS '16.30', LEVEL1)#,##0.00) as level4

Re: Change row value

Anil_Babu_Samineni — Tue, 31 Jan 2017 07:40:26 GMT

May be use Num funciton like below

Num(IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2, LEVEL1),'00.00') AS LEVEL4

Re: Change row value

Tue, 31 Jan 2017 07:42:36 GMT

My excel is like this

I know my excel does not contain 16.30 but as mentioned, it treats 16.3 as 16.30.(I think it is some formatting issue)

when I use this code

IF(LEVEL2 = '16.30' AND (len(LEVEL3 > '0')), LEVEL2, LEVEL1) AS LEVEL4

It works and it loads into qlikview as shown below

As you can see, 16.3 is loaded with the above code. However, I want to display it as 16.30 because my boss wants it displayed as such. How do I manually change it to show as 16.30?

Re: Change row value

stabben23 — Tue, 31 Jan 2017 07:47:02 GMT

So then Your if statment wont work, 16.30 doesent exsist. Try this instead.

IF(LEVEL2 = '16.3' AND (len(LEVEL3 > '0')), num(LEVEL2,'##,##'), LEVEL1) AS LEVEL4

Re: Change row value

sdmech81 — Tue, 31 Jan 2017 07:55:33 GMT

This should work

Num(IF(LEVEL2 = '16.3' AND (len(LEVEL3) > '0'), LEVEL2, LEVEL1),'#,##0.00') AS LEVEL4

Try!

Re: Change row value

stabben23 — Tue, 31 Jan 2017 08:01:20 GMT

What is Your DecimalSep?

Is it a . or a ,?

if it is a . use this:

IF(LEVEL2 = '16.3' AND (len(LEVEL3 > '0')), num(LEVEL2,'##.##'), LEVEL1) AS LEVEL4

if it is a , use this:

IF(LEVEL2 = '16.3' AND (len(LEVEL3 > '0')), num(LEVEL2,'##,##'), LEVEL1) AS LEVEL4