QlikView mergin data in Error

[#1]

Hello,

I am loading from a dbf file and I noticed an issue I cannot find a way to solve and perhaps someone can help me: I have a customer account number 19115, I also have another customer 00019115. When I load the data on my script, if I use Distinct then both records show as 00019115, I know both are showing because they both have different Tax rate. If I don't use Distinct then both records appear as 19115. This is causing a problem with my key because QlikView is merging both records and either eliminating the '0' or adding the '0'. I tried adding the record number to see if this will make a difference and I got the same results.

Here is my code:

SELECT Distinct
RecNo(), account , store, tax1, tax2 FROM Customer

Can somebody help me solve this please?

Thank you,

Hugo

[Peter_Cammaert · Accepted solution]

Use the text() function when loading the customer ID. This forces QlikView to forget the numerical representation and keep the string representation (both 19115 and 00019115).

An easier (for you at least) method would be to prefix all customer id's everywhere with a character or a string
(e.g. 'C' & CustomerID). This - again - forces QlikView to not try treating customer IDs as numbers but as strings.

Best,

Peter

PS. It's not the SELECT that is mistreating your data. It's QlikView that is doing this, even without an explicit preceding LOAD. Add a preceding LOAD with a text() call for every field you want to keep as a string, like:

Customers:

LOAD RowNo() AS RNo, text(account) AS account, store, tax1, tax2;

SQL SELECT account, store, tax1, tax2 FROM Customer;

[#3]

Thank so much Peter, I have been trying to solve this for quite a few hours on my own without any luck. Your sample code works perfect and your explanation also help me identify other areas where my code needs to be changed to make sure QlikView treat those fields as strings.

Regards,

Hugo