Data Type Formats Mismatch between QV and QS

[AnnaRodriguez]

We are migrating our Qlikview processes to Qliksense and noticed a problem in the way Qliksense interprets data type of an attribute.

Our database table (mytable) has Department as STRING (varchar). It contains numbers, strings, mixed values, numbers with leading zeros ..etc. When we load this table into Qlikview, Qlikview automatically translates number (like) values into numbers and varchars into text. For example using:

LOAD Retailer, Department from mytable; 

Department values like 011, MISSY, 230-20,35 are loaded in QV:

• 11 (number right justified)
• MISSY (text left justified)
• 230-20 (text left justified)
• 35 (number right justified)

 We are not using any function to load Department at all in QV.

When i translated the same load in QS, the same values are treated as text only and are right justified:

• 011 (text left justified)
• MISSY (text left justified)
• 230-20 (text left justified)
• 35 (text left justified)

This mismatch has created a problem for history data where QVD loaded off Qlikview had 11 as Department value, but now in QS it is 011. 

Is there any way to have Qliksense load such data in the same way as Qlikview does?

[ElisaF]

Hello Anna,

The problem with how QlikView or QlikSense interprets the data type of an attribute when loading data from a QVD file is quite old and has caused me quite a few problems, especially with varchar fields.
I found a very "elegant" technical solution at Rob Wunderlich. See the link below:

https://qlikviewcookbook.com/2016/07/touchless-formatting/

In the case of your problem, load dummy field before loading data from QVD file to assign the attribute to the Department field

TempFormatTable:
LOAD
Num(0) as Department
AutoGenerate 1;

Data:Load Department, ... from Data.QVD

DROP Table TempFormatTable;

Please test the solution and send me your feedback.

Regards,

Eliza

[AnnaRodriguez]

This is a very nice suggestion, but it did not work for me. I had Departments values still loaded as 532 and 0532. I expected them to be consolidated into 532.

[rwunderlich]

Are you loading using Qlik Data Gateway?

-Rob

[AnnaRodriguez]

no, i am using Qliksense on Windows.

[AnnaRodriguez · Accepted solution]

I used num# function on Department and that seemed to consolidate num and varchar values into num (right justified). The problem is that any history QVDs had to be also updated to match the structure.