topic numeric value in excel files into Qlikview..list box is showing duplicates in QlikView

numeric value in excel files into Qlikview..list box is showing duplicates

QlikviewRaj11 — Mon, 14 Sep 2020 20:49:42 GMT

Hi All,

I have this dataset in Share Point and I load multiple excel files into QVD... one of the field in these excel files is "Total"

which includes values like 0.000, 3.111 etc... I load all excel files into the QVD and for the field "Total" I used the

NUM(Total,'####.###') AS Total - When I read from the QVD into the dashboard I use "Total" as filter using the listbox

for some reason I see the duplicate numbers....the below is an example

the second filter book is the number of the excel file so there are so many excel files for the PRD environment

for each LOAD statement from excel to QVD I used the NUM function but I still get duplicates

any ideas on why I would see the duplicates?

This is how it looks like in my excel file

Thanks a lot

Re: numeric value in excel files into Qlikview..list box is showing duplicates

Oleg_Troyansky — Mon, 14 Sep 2020 21:55:58 GMT

Hi Raj,

It looks like your data contains very similar, but slightly different numbers, where the difference exists beyond the three decimal points. For example, the following 2 numbers are different, but they look identical when they are formatted with 3 decimal digits:

3.3071 looks like 3.307

3.3072 also looks like 3.307

The num() function that you use, merely formats the numbers to the desired data format, however it doesn't modify the values themselves. In order to make these two numbers truly identical, you need to use one of the rounding functions - round(), floor(), or ceil(), like this:

round(RawData_Total, 0.001)

Best,

Re: numeric value in excel files into Qlikview..list box is showing duplicates

QlikviewRaj11 — Mon, 14 Sep 2020 22:13:56 GMT

Thank you Oleg,

Num( Num#(Total, '0.000'), '####.###') AS Total - This worked but to be more accurate I used round as below - thanks for your time

Num(Round(Total,0.001), '####.###') AS Total1,

Thanks a lot

Raj