Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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