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