10 Replies Latest reply: Mar 7, 2017 10:33 AM by Sunny Talwar RSS

    convert subfield format into numeric

    Giacinto Abbruzzese

      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?