Hi
The unique identifier for items "ItemNo" in our database (DB2) is containing only numbers (but is stored in the database as varchar). Some item numbers are having leading zeros, for example item 00123 which is not the same as item 0123 or item 123.
Normally we solve this issue in QLIKVIEW LOAD by using the text() function, to keep leading zeros after the load.
For example
LOAD
text(ItemNo) as ItemNo
FROM … etc
The problem is that when using Direct Query we seem to not be able to use QLIKVIEW functions, hence the ItemNo field is formated as number(since it only contains numbers), and we loose the ability to differentiate the items 00123,0123, 123 because they all are read as 123.
DIRECT QUERY
DIMENSION
ItemNo
MEASURE
Weight
FROM DB2.Database
We tried using preceeding load like this:
LOAD text(ItemNo) as ItemNo;
DIRECT QUERY
DIMENSION
ItemNo
MEASURE
Weight
FROM DB2.Database
It did not work….
We also tried using NATIVE function like this:
DIRECT QUERY
DIMENSION
NATIVE('CAST (ItemNo as VARCHAR(5))') as ItemNo
MEASURE
Weight
FROM DB2.Database
Did not work either. QLIKVIEW still only see numbers and remove the leading zeros...
How can we make sure fields are in TEXT format using DIRECT QUERY?
Regards
Robert
Svebeck Consulting AB
