topic Re: Counts not working properly on field with decimals in QlikView

Counts not working properly on field with decimals

JChamber — Sat, 16 Nov 2024 18:48:45 GMT

Hello,

I have two tables that are associated through a field (called prindiag) which has text characters, and the counts of that field are not working correctly. I believe that the prindiag field is the problem, as its values have a mix of alpha and numeric characters, some with decimals, and leading and trailing zeros. E.g., it has values like 008, 008.0, and 008.00. What I'm seeing, for example, is that if 2 records have values of 008.00 (and no records with either 008 or 008.0), it ends up returning a count of 6 ( 2 records for each for 008, 008.0, and 008.00), which is the incorrect total.

The sample script .qvw file attached and script below illustrate the problem. In that sample data, I would expect a count of 2 for each of the prindiag codes, but the 008, 008.0, and 008.00 each have a count of 6.

It seems as if Qlikview is treating the number as an integer and ignoring the decimals and trailing zeros. Consequently, it's interpreting the value 008 as being identical to 008.0 or 008.00, when they are not. I have tried many solutions (and searched the Forum for others), including converting prindiag to text (using TEXT()), replacing the decimal and creating a new key with concat, but nothing so far has worked.

Any solutions or advice you may have to offer would be greatly appreciated.

Best,

-John

FactSource:
LOAD * INLINE [
sys_recid, prindiag
1, 007.1
2, 007.1
3, 007.4
4, 007.4
5, 008
6, 008
7, 008.0
8, 008.0
9, 008.00
10, 008.00
];

Diag_def:
LOAD * INLINE [
prindiag, description
007.1, 007.1 - GIARDIASIS
007.4, 007.4 - CRYPTOSPORIDIOSIS
008, 008 - INTESTINAL INFECTIONS DUE TO OTHER ORGANISMS
008.0, 008.0 - INTESTINAL INFECTION DUE TO ESCHERICHIA COLI
008.00, 008.00 - INTESTINAL INFECTION DUE TO UNSPECIFIED E COLI
];

FactSource2:
NoConcatenate LOAD
sys_recid,
prindiag
RESIDENT FactSource;
DROP TABLE FactSource;

Diag_def2:
NoConcatenate LOAD
prindiag,
description
RESIDENT Diag_def;
DROP TABLE Diag_def;

Re: Counts not working properly on field with decimals

sunny_talwar — Wed, 08 Apr 2020 13:48:22 GMT

Try this

FactSource: LOAD sys_recid, Text(prindiag) as prindiag; LOAD * INLINE [ sys_recid, prindiag 1, 007.1 2, 007.1 3, 007.4 4, 007.4 5, 008 6, 008 7, 008.0 8, 008.0 9, 008.00 10, 008.00 ]; Diag_def: LOAD Text(prindiag) as prindiag, description; LOAD * INLINE [ prindiag, description 007.1, 007.1 - GIARDIASIS 007.4, 007.4 - CRYPTOSPORIDIOSIS 008, 008 - INTESTINAL INFECTIONS DUE TO OTHER ORGANISMS 008.0, 008.0 - INTESTINAL INFECTION DUE TO ESCHERICHIA COLI 008.00, 008.00 - INTESTINAL INFECTION DUE TO UNSPECIFIED E COLI ]; FactSource2: NoConcatenate LOAD sys_recid, prindiag AS prindiag RESIDENT FactSource; DROP TABLE FactSource; Diag_def2: NoConcatenate LOAD prindiag AS prindiag, description RESIDENT Diag_def; DROP TABLE Diag_def;

Re: Counts not working properly on field with decimals

rubenmarin — Wed, 08 Apr 2020 13:55:53 GMT

Hi, I think text() is the solution, check attached document. Text() should be used from the inital load of data.

Edit: Hi Sunny, I forgot to reload the tab before answering.😅

Re: Counts not working properly on field with decimals

JChamber — Wed, 08 Apr 2020 14:15:23 GMT

Brilliant! Thank you much, Sunny and Rubenmarin. I had used Text(), but not on the initial load, and that was why it wasn't working for me. The counts are now working correctly. Much appreciated, both of you.