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? 

[Melissa_Potvin]

is this for Qlik Sense or QlikView?

[Anonymous]

Qlik Sense.

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

[swuehl · Accepted solution]

Have you tried to round your numbers to two decimals instead of just formatting?

Something like

Load *, Round(CHECK1, 0.01) as CHECK

[Anonymous]

Yes, thank you.

That worked!