topic How to modify the values in a table in QlikView

How to modify the values in a table

Tue, 02 Sep 2014 08:10:49 GMT

Hi everyone !

I'm pretty new to qlikview and I'm trying to get a hold of this software that looks awesome. The question I have is the following :

I have uploaded an excel file and created a table out of it
I took some values of this table to create a new table in order not to change anything inside the first one (I want to keep it the way it is)
I want to modify the values of the second table. For instance, I have expressions such as 100 % BRSS - 200 % TM and I want to trim the expression in order to remove the spaces inside. Then I want to separate the expression before the minus, in order to create two results put in different columns : one with 100%BRSS and one with 200%TM

I don't exactly know how and where to do this on qlikview. And how to do it for every line of the table. Could someone give me an explanation on this ?

Thanks in advance !

Re: How to modify the values in a table

Anonymous — Tue, 02 Sep 2014 08:21:57 GMT

Try something like :

load

mid ( PurgeChar ( '100 % BRSS - 200 % TM' , ' ' ) , index ( PurgeChar ( '100 % BRSS - 200 % TM' , ' ' ) , '-' ) +1 ) as [FirstBit] ,

mid ( PurgeChar ( '100 % BRSS - 200 % TM' , ' ' ) , 1 , index ( PurgeChar ( '100 % BRSS - 200 % TM' , ' ' ) , '-' ) -1 ) as [SecondBit] ,

.............. etc.............

You'll find descriptions of the PurgeChar, index & mid functions in the QV Desktop Help

Re: How to modify the values in a table

Tue, 02 Sep 2014 08:36:15 GMT

Thank you very much for your answer Bill !

But as I said earlier, I am brand new to qlikview. Where do I have to writte down these command lines ? Because it doesn't seem to work when I write this down in the script.

Thanks again

Re: How to modify the values in a table

Tue, 02 Sep 2014 08:38:28 GMT

You can also use below expression where Data is the column name

PurgeChar(Left(Data,Len(Data)-FindOneOf(Data,'-')),' ') as Left,

PurgeChar(Right(Data,Len(Data)-FindOneOf(Data,'-')),' ') as Right

Re: How to modify the values in a table

Anonymous — Tue, 02 Sep 2014 08:40:38 GMT

You need them in the load script as you say you did.

When you say 'not work', what kind of not work ? Could you share the error message and script ?

..............