Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to modify the values in a table

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 !

4 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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 ?

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