Bug or *feature by design*? Excel rand() & QV load

[#1]

Hi,

While I'm doing some tests here I noticed a strange thing with QV.

What I did is a sample xlsx for some random data - random ID from 0-4 in one column. Saved as an xlsx and load the file in QV. Worked with ApplyMap - but then it becomes strange:

First of all: ApplyMap is not working. Check the IDs box: No grouped values (for example, multiple 0!). So I saved the file under an CSV to get rid of the rand() function in the xlsx - everything's working as expected.

The main question is now: How does QV handles xlsx with rand() (or to be more general: with functions that could change a value). In my opinion it's ok that QV does a refresh on the document before it will load the data. But why didn't QV recognize same values and group them...?

Tested with QV11.

[Miguel_Angel_Baeyens]

Hi,

When numbers (values that are right aligned by default) are repeated (several zeroes in your example) sounds like they have decimals, although they are being represented as integers. Use instead

ApplyMap('LadenMap', Floor(Laden), 'not found') AS Bezeichnung,

So the Floor() function returns the integer part of the RAND() number generated by excel.

Hope that helps.

Miguel

[#3]

Ouch - completely forgot that I formatted the cell format.

But that means that QV is taking .value instead of .value2. Faster but it will ingore the format of a cell...not sure what to think about it, but seems a bit confusing for people who are working with a cell formating.