Counts not working properly on field with decimals

[JChamber]

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;

[sunny_talwar · Accepted solution]

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;

[rubenmarin]

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.😅

[JChamber]

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.