how can you change the format of a subfield (string-)variable into a numeric variable?
I had to use the subfield command in order to convert SharePoint based data.
Let me give you an example:
If you load SharePoint data (e.g. field = ID with values 1, 2, 3 etc.) it returns =
to convert it back i've used the command:
subfield([ows_ID], '#', 2) as ID
That's totally fine for me, but what can i do in order to use such a field as an expression (e.g. Sum, Avg. etc)?
SharePoint Load format:
subfield([ows_Sales], '#', 2) as Sales returns
but when i want to use the field Sales as an expression (Sum) it Returns 0, because it is still a string.
Solved! Go to Solution.
thanks again, but that also didn't work out.
Variant 1 returned in table as a dimension missing values '-' and '0,00' as an expression Sum [Sales returns]
Variant 2 returned in a table the right values as a dimension, but '0,00' as an expression Sum [Sales returns 2]
Hi Dear Giacinto
Try this option num(Num#(SubField([ows_Sales], '#', 2), '#', '. ',', ') ) as [Sales returns]
num(evaluate([ows_Sales]) *1) as [Sales returns]
Num#(Replace(Trim(SubField([Sales return raw], '#', 2)), '.', ',')) as [Sales Return]
as well as
num(Num#(SubField([ows_Sales], '#', 2), '#', '. ',', ') ) as [Sales returns]
Thank you Sunny and Marcos!
On a sidenote: My SharePoint list data contains data with + and - amounts of values (€) for the same ID.
ID [Sales return]
3 4000 €
4 3000 €
4 -3000 €
Qlik doubles both row amounts for one specific ID with Sum(expression).
Instead of doubling such amounts, it should sum in case of ID 2 and 4 to 0 €.
Sadly, i can't change the raw format of the SharePoint list data, so i need a way to recode it within the qlik data editor.
How can i prevent summing or doubling such amounts for one ID? Obviously Qlik interprets the raw data all as positive amounts, although the values consists for some ID's as negative and positive amounts row-wise.