topic Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app in Data Movement & Streaming

Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

EspenH — Wed, 19 Mar 2025 21:46:28 GMT

We have a case where the formatting of a column is somehow changed from initial loading through middle step staging in qvds where we also do a MERGE to finally being loaded in an app. In the app we have values ("numbers") interpreted as both numbers and strings?

Initially we load the data from an MS SQL database. In the database the column is a nvarchar(20), not null column. The initial load are stored as a qvd file. This file is then MERGED with another file from MS SQL server with the same format in the data base nvarchar(20), not null.

The table is also used in a link table with the specific column, which is created in the load script in the app.

When I open the app and do a search in the mentioned column i find the same "numbers" both as a integer and as a strings. In the search window in a straight table the same number appear on both left and right side...

Do any of you experts know at what time in my proses this may change?

One theory is that the merged source file (the changes which is bering merged) only contain integer numbers as opposed to the target file its being merged with which contain both integers and strings...?

I have manage to solve this by specifying the column as text() in the load script in the app, but would like to know how this may happen?

Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

marcus_sommer — Wed, 12 Mar 2025 13:32:33 GMT

Qlik doesn't follow the concept of data-typing else it used a data-interpretation which depends on the interpretation-variables respectively the OS region settings and the load-order - because the first loaded field-value triggers the interpretation. The source is completely irrelevant.

This means your mismatch may occur within any processing-stage. Each slightest change may have an impact. There is no globally sensible rule to handle the matter else it will depend on the specific case which approach is the most suitable.

Usually it's not a big task else the data-interpretation worked quite smoothly and the few exceptions of a mismatch could be explicitly treated with a text() or num#() or an appropriate pre-load and/or an adjusting of the order of the processing-steps.

Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

EspenH — Wed, 12 Mar 2025 13:47:08 GMT

Thank you for replying and a god explanation.

According to your explanation I belive my theory may stick regarding my MERGE stage?

Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

marcus_sommer — Wed, 12 Mar 2025 14:09:19 GMT

It might be caused from anywhere. Depending on the various merging-types (concatenating as well as join/mapping) the possible causes may already be limited and easily detectable in the final app. If not it might be necessary to look into each step whereby starting in the middle and opening the qvd's with a third-party tool or loading them into an empty dummy-app is quickly done.

Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

EspenH — Wed, 12 Mar 2025 14:11:53 GMT

Merging in this context is not merging values in a table but using the functionality MERGE for handling incremental update of qvd files..

Re: Numer formating, from database loading to qvd staging and qvd loading in app - with different formats in app

marcus_sommer — Wed, 12 Mar 2025 14:31:57 GMT

Each kind of an incremental load could change the load-order and/or the order of the loaded field-values. Especially if any partial loads are included - because only certain script-parts are executed which may not know / inherit the data-interpretation of the full script - and needing therefore appropriate explicit (extra) statements.