topic Re: Is my replace expression working or not? in QlikView

Is my replace expression working or not?

Fri, 12 Jul 2013 10:19:38 GMT

Hi, i'm new to qlikview and testing some things out with the demo version of qlikview. We will be purchasing the real version next year so i wanted to get a headstart as i will be in charge of implementing qlikview for our company. The idea is that the dimension 'UK' is replaced with 'EU'(this works) and that blank dimensions are replaced with the value '_NVT'.(this seems to work only partly)

So i have tried the following script lines to clean up some of our data. But none of them seem to work, or are they?

replace(replace("Gen_ Bus_ Posting Group", null(),'_NVT') ,'UK','EU') as "Global Dimension",

replace(if(isnull("Gen_ Bus_ Posting Group")=0, "Gen_ Bus_ Posting Group", 'BINNENL') ,'UK','EU') as "Global Dimension",

replace(if(len("Gen_ Bus_ Posting Group")=0,'_NVT') ,'UK','EU') as "Global Dimension",

I have also tried repalce isnull() with ' ' and 0 but i have been unable to fix it still.

I cannot share the qlikview file as it includes a lot of our companies' sensitive information.

The end result can be observed in the attached printscreen.

I am unable to select the brown part of the graph and by filtering i can actually see that there are no longer any values containing blank dimensions. So im guessing it sort of works, but why is my graph going wrong? and is there something i can do about it, preferably id not have the empty dimension display at all, so how would i go about doing that?

Re: Is my replace expression working or not?

Gysbert_Wassenaar — Fri, 12 Jul 2013 10:36:26 GMT

Try:

if(len(trim("Gen_Bus_Posting Group"))=0,'_NVT', replace("Gen_Bus_Posting Group",'UK','EU')) as "Global Dimension"

Null values cannot be selected in Qlikview. That's why you can't select the brown areas. Replacing nulls with real values is a good idea.

Re: Is my replace expression working or not?

er_mohit — Fri, 12 Jul 2013 10:45:04 GMT

i think in chart you have to check on suppress null value for dimension side

Re: Is my replace expression working or not?

Fri, 12 Jul 2013 14:39:11 GMT

Thing is when i turn OFF the suppress null value option in the graph properties the bug actually dissapears and i get a correct looking layer of brown nothingness on top of my graph.

Re: Is my replace expression working or not?

Fri, 12 Jul 2013 14:44:10 GMT

Thanks for the swift reply, i combined both your replies and this fixed the problem. When using my own line the suppress option would bug out, but with your line it doesnt.

Re: Is my replace expression working or not?

Fri, 12 Jul 2013 14:47:12 GMT

Oh, False Alarm, i got excited too fast, its still not gone T.T