Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Have you tried to round your numbers to two decimals instead of just formatting?
Something like
Load *, Round(CHECK1, 0.01) as CHECK
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
Have you tried to round your numbers to two decimals instead of just formatting?
Something like
Load *, Round(CHECK1, 0.01) as CHECK
Yes, thank you.
That worked!