Text to financial value conversion & sumif of the converted value

[Daniel_K]

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.

[Anonymous]

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]

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]

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]

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 · Accepted solution]

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? 

 

[Daniel_K]

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 · Accepted solution]

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$);