After calc on Double String and 32 bit Integer + String TYPE recs gives a -0,00.

[Anonymous]

 I am doing a calc on a field that has some records with a TYPE of DOUBLE + STRING and some of TYPE 32 bit Integer + String. The calc works and give 0,00 if the recs are of TYPE 32 bit integer +STRING. It gives a result of -0,00 when it has a record TYPE of DOUBLE+STRING. I tried formatting all input amounts with MONEY format and still does not work. How can I get the 0,00 result without the -0,00? Thank you! Is there also a way to check the field if there is a -0,00 and then make it 0,00? 

[Gysbert_Wassenaar]

Hi,

I've never heard of a data type "DOUBLE + STRING".  Can you explain what kind of data type that is? What kind of system does that come from? Qlik Sense knows only number and text as data types. Perhaps you should make sure you load everything explicitly as text using the text() function and then use the num#() function to create numbers from the strings.

Can you give some examples of the data values that give you problems?

[Anonymous]

I see it with this type in Q-EYE software. Is there a way to upload a Word document to provide an example?

[Anonymous]

Here is an example:

Input file has a field ([.AMOUNT]) that is forced to MONEY and saved as WBS_AMOUNT_SE:

CODE: MONEY ([.Amount]) as WBS_AMOUNT_SE

WBS_AMOUNT_SE Record 1: 111,40

WBS_AMOUNT_SE Record 1: 132,71

I use software called Q-EYE that looks at the qvd file.

In Q-EYE:

WBS_AMOUNTS_SE Record 1: 111,40 shows TYPE of DOUBLE+STRING

WBS_AMOUNTS_SE Record 1: 132,71 shows TYPE of DOUBLE+STRING

Then WBS_AMOUNT_SE is multiplied by 1000 and saved as AMOUNT_1K

CODE: Load *, money(WBS_AMOUNT_SE * (1000)) as AMOUNT_1K

ISSUE:

But then after I do the calc (WBS_AMOUNT_SE * 1000) and store as AMOUNT_1K it gives different result TYPES:

AMOUNT_1K (111.400,00) has a TYPE of 32 BIT Integer + STRING

AMOUNT_1K (132.710,00) has a TYPE of DOUBLE + STRING

Note: the numbers visibly look correct in Q-Eye and Excel.

When I do a calc on AMOUNT_1K - SUMSAP, for one I get 0,00 and the other one get -0,00.

The record that had TYPE DOUBLE+STRING after it was multiplied by 1000 gives a -0,00.

The other one that started with TYPE DOUBLE+STRING and changed to 32 BIT Integer + STRING TYPE gives a correct result of -0,00.

CODE: Load *, money((AMOUNT_1K) – (SUMSAP)) as CHECK1

AMOUNT_1K (111.400,00) – SUMSAP (111.400,00) = 0,00

AMOUNT_1K (132.710,00) – SUMSAP (132.710,00) = -0,00

I also try to force if CHECK1 is (-0,00) to change it to CHECK (0,00) but still gives (-0,00):

CODE: Load *, IF (CHECK1 = ‘-0,00’, ‘0,00’, CHECK1) as CHECK

[jonathandienst · Accepted solution]

Integer arithmetic gives exact results. Floating point arithmetic usually provides approximate answers (with errors in the least significant digits -- about a  part per trillion). In your case, you have a value of probably around -10^-12. Qlik uses standard ISO floating point - read more on the web.

Use Round(expr, 10^-9) or similar to get exact results.

[Anonymous]

That was it! Thank you!

Updated code worked:

Load *, money(round(CHECK1)) as CHECK