Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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!