Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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$);
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
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
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;
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
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?
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
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$);