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]

Just do like this

[row]:

LOAD ID,

           Sum([Sales Return])

Group By ID;

LOAD subfield([ows_Pipeline_x0020_ID], '#', 2) as [ID],

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

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_row]);