Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

convert subfield format into numeric

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?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: convert subfield format into numeric

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.

10 Replies
MVP
MVP

Re: convert subfield format into numeric

Try like this

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

or

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

gino2780
Contributor

Re: convert subfield format into numeric

Both suggestions return missing values ( - ) 

MVP
MVP

Re: convert subfield format into numeric

How about this

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

or

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

gino2780
Contributor

Re: convert subfield format into numeric

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]

MVP
MVP

Re: convert subfield format into numeric

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
Contributor

Re: convert subfield format into numeric

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
Contributor

Re: convert subfield format into numeric

Sure. Here you are. See the attachement.

MVP
MVP

Re: convert subfield format into numeric

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
Contributor

Re: convert subfield format into numeric

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.