Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_K
Contributor II
Contributor II

Text to financial value conversion & sumif of the converted value

Hello,

A couple of issues regarding a data set that I need help with.

First of all the financial values have loaded as text and I need to convert them to a numerical value, the current text layout is £ 1,000.00 (one space between the £ and the 1), I've tried Money#() but it doesn't seemed to have worked for me.

The second issue is that once the conversion from a text value to a numerical value has occurred I need to feed the numerical value into a sumifs equivalent formula.

Please find attached an example of the data and the outcome I'm looking to achieve.

Labels (1)
2 Solutions

Accepted Solutions
Anonymous
Not applicable

I used your table and made an example.  See the attached example

Then I build a pivot table like below with 2 dimensions and 4 formulas 
Is this a help for you? 

 Community.jpg

View solution in original post

Anonymous
Not applicable

Have a look into the script of my example, where the conversion takes place:
Account:
first 14
LOAD [Account Name],
money(keepchar(Amount,'01234567890,.')) as Amount,
[Potential headline],
Renewed?,
[Account manager]
FROM
[C:\Qlikview\TEST_Community.xls]
(biff, embedded labels, table is Sheet1$);

View solution in original post

7 Replies
Anonymous
Not applicable

try to work only with numbers. the mocey format comes from your definition

inmy example: =money(keepchar('$ 1.000,00','01234567890,.')+10) (just to show that you can work with the result of keepchar

the result is 1.010,00 €

What you wwant to do with sumif? you have numbers and can work with them

Daniel_K
Contributor II
Contributor II
Author

Hello ,

Am I supposed to apply =money(keepchar('$ 1.000,00','01234567890,.')+10) as an expression or in the script? with either option please could you let me know how I'd put it together with the columns I have provided.

The numbers I've mocked up are put together in excel and I need to know how to replicate the formulas in Qlik View. If the solution amends the value from text to a number in the script I can work with it but if I need to amend the text to a number via an expression I needto know how to embed the text to number expression into a sumif expression.

Thanks,

Daniel Knight

Anonymous
Not applicable

you can put it in the script: I provided a small example. In a textbox I use sum(NewVal) and the result is 6.666,66 €.
Does it gives you an idea? If not specify more or give example what you want to achieve

TMP:
Load * Inline [
Value
$ 1.111,11
$ 2.222,22
$ 3.333,33
];

TEST:
NoConcatenate load
money(keepchar(Value,'01234567890,.')) as NewVal
Resident TMP;

drop table TMP;

Daniel_K
Contributor II
Contributor II
Author

Hello,

In the script I'm loading an excel spreadsheet from a folder which updates daily so the individual values aren't shown in the script, just the column name [Expected Revenue], so I don't see how your below suggestion will work.

Please find attached an example of what I'm trying to achieve, the 1st 13 rows are the raw data with column B being the column containing text currently which is stopping me from performing sum expressions.

Rows 16 to 24 are the result I'm looking to achieve.

Thanks,

Dan

Anonymous
Not applicable

I used your table and made an example.  See the attached example

Then I build a pivot table like below with 2 dimensions and 4 formulas 
Is this a help for you? 

 Community.jpg

Daniel_K
Contributor II
Contributor II
Author

Hello,

Using a pivot table helps with the sumifs but I still need to find a solution to converting the text to a number for the value sum to work.

From investigating further it looks like I need to use either Money# or Num# but I can't get either to work in an expression.

Thanks, for your help so far.

Dan

Anonymous
Not applicable

Have a look into the script of my example, where the conversion takes place:
Account:
first 14
LOAD [Account Name],
money(keepchar(Amount,'01234567890,.')) as Amount,
[Potential headline],
Renewed?,
[Account manager]
FROM
[C:\Qlikview\TEST_Community.xls]
(biff, embedded labels, table is Sheet1$);