convert subfield format into numeric

[gino2780]

Hello,

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:

Example 1:

If you load SharePoint data (e.g. field = ID with values 1, 2, 3 etc.) it returns =

ID

1;#1

2;#2

3;#3

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)?

Example 2:

SharePoint Load format:

Sales

1000;#1000

2000;#1000

3000;#3000

subfield([ows_Sales], '#', 2) as Sales returns

Sales

1000,

2000,

3000

but when i want to use the field Sales as an expression (Sum) it Returns 0, because it is still a string.

Any suggestions?

[sunny_talwar]

Try like this

SubField([ows_Sales], '#', 2) * 1 as [Sales returns]

or

Num(SubField([ows_Sales], '#', 2)) as [Sales returns]

[gino2780]

Both suggestions return missing values ( - ) 

[sunny_talwar]

How about this

Trim(SubField([ows_Sales], '#', 2)) * 1 as [Sales returns]

or

Trim(Num#(SubField([ows_Sales], '#', 2))) as [Sales returns]

[gino2780]

Hello Sunny,

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]

[sunny_talwar]

Would you be able to share an app where you are trying to do this or share a sample in Excel file of an extract from your app so that I can test this out

[marcos_herrera]

Hi Dear Giacinto

Try this option num(Num#(SubField([ows_Sales], '#', 2), '#', '. ',', ') ) as [Sales returns]

Or

num(evaluate([ows_Sales]) *1) as [Sales returns]

[gino2780]

Sure. Here you are. See the attachement.

[sunny_talwar · Accepted solution]

Try this:

Num#(Replace(Trim(SubField([Sales return raw], '#', 2)), '.', ',')) as [Sales Return]

The issue was that your Decimal separator is set to a comma.

[gino2780]

It works!

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.

Exsample data:

ID          [Sales return]

1            1000€

2            2000€

2           -2000€

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.