QVD and CSV issues

[FrancescoStefanni]

Dear all,

during the development of our apps we have encountered some strange issues by using Qlik Sense.

First of all, let me describe the configuration:

• We are using Qlik Sense February 2019 patch 4
• Our Postgres DB have all values stored as STRINGS, in a table whose columns are (more or less) Timestamp, MetricID, Value. This is because NOT all our stored values are numbers.
• Our environment is configured as Italian (so uses comma as decimal separator for numbers), but in our Postgres DB the numbers are stored represented by using the dot (".") for decimal separator (in order to have a environment independent format).

The workflow is as follows:

1. Importing "as is" the values from Postgres, and storing into a QVD.
2. Read the QVD, filtering out the metrics which we know have actual string values (so we preserve only "numeric" values). [Let's name this QVD#1]
3. Converting the remaining values to numbers: num(num#(Value, '#.#', '.', ','))
4. Storing the converted values into a QVD and into a CSV. [Let's name these QVD#2 and CSV#2]
5. Load the QVD, perform some aggregation on values, such as SUM(), AVG(), STDEV().
6. Storing the results into a QVD and a CSV [Let's name these QVD#3 and CSV#3]

Here the description of the strange behaviors:

• Behav#1: With this workflow, everything seems to go fine, except if we open into the Data Model Viewer the QVD#3, it does not shows the values (it shows "?"). Nevertheless, the resulting plots seem fine. Please note that QVD#2 is shown correctly.
• Behav#2: If, in the step 3 of the flow, instead of using num(num#(Value, '#.#', '.', ',')), we use num#(Value, '#.#', '.', ',') to convert the numbers, the generated CSVs and QVDs have anomalies, as question marks, some numbers are recognized as strings in the model viewer (and some are viewed with dot as separator, others with comma). Also into the CSV there are some anomalies: numbers have different formats (some with comma, others with dot).

From my (very basic) understanding, some issues are due to the dual values management of Qlik. For example, it is likely that the printing of the CSV uses the string representation of the dual values (this should explain BEHAV#2 for CSV).

On the other hand, I am not expecting to have issues with the QVD, since it is a binary format (so I hope numbers are "auto-magically" handled). So I do not get why  I see question marks (BEHAV#1), or mixed number representations in the model viewer (BEHAV#2 for QVD).

So, can someone explain me why I have these issues with the QVD?

Best regards.

[marcus_sommer]

If the data are shown as question marks (?) it means that the values have no string-representation. There are probably several reasons possible and one is the use of a converting-function like num#() because it results in a binary-value - if there isn't any formatting/interpretation explicitly set or inherited from a previous handling on this field there is no information how these values should be displayed and therefore the ? is applied as a placeholder.

Your statement to the mixed numbers isn't complete clear to me - does it mean the display in multiple measure-fields are different and/or they are different within a single field. For both cases are various causes thinkable, for example if the data are merged from various sources and if so are there any convertings/formattings applied. Also the order of the statements could have an impact.
As the main-reason I could imagine again the num#() because the converting-functions worked only if the applied format-pattern are absolutely correct - if not they return the origin-value. This means one value might be converted and the other not depending of the varity of the values.

In your case it might be useful to apply multiple converting and also to apply always a formatting on them - which have the benefit/disadvantage that if the converting failed the result will be NULL and not the origin string. And this might be look like:

num(alt(num#(Field, 'format1'), num#(Field, 'format2'), num#(Field, 'format3'), 'any default'), 'final format')

- Marcus

[FrancescoStefanni]

Thank you for your reply.

Yes, forcing formatting is an interesting idea. I have tried to apply it,  but I still have some question marks. Maybe I am missing some locations in my scripts where I calculate new values.

Since everything else seems fine, I'll just skip this issue to proceed with my work.

Best regards.