Format field as text in direct query?

RSvebeck — Wed, 03 Oct 2018 12:39:17 GMT

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

Re: Format field as text in direct query?

RSvebeck — Sat, 20 Oct 2018 10:21:53 GMT

Bump.

topic Re: Format field as text in direct query? in QlikView

Format field as text in direct query?

Re: Format field as text in direct query?