Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ppCNH
New Contributor II

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

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? 

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: After calc on Double String and 32 bit Integer + String TYPE recs gives a -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

 

4 Replies
Community Manager
Community Manager

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

is this for Qlik Sense or QlikView?
ppCNH
New Contributor II

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

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

Highlighted
MVP
MVP

Re: After calc on Double String and 32 bit Integer + String TYPE recs gives a -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

 

ppCNH
New Contributor II

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

Yes, thank you.

That worked!