Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data load from SQL database not as i need

Hello, hope someone can help with little issue I've come across.

We are pulling data into Qlikview from a SQL DB via ODBC connection etc.  I've noticed issue with one particlar field I am importing in how qlikview is handling the data within the field.

the field in question is "Equipment Code" and is use to tag assets wihin our building which we plan maintenance on.  I found 2 equipment numbers which are as follows: "002281" and "0002281". if you ignore the zero's you could argue they are technically the same number, but in the database they are two records.  The equioment code field is a char data type not number/interger etc so it allows these as 2 unique entities!

Problem is when I load the script in Qlikview and look at these in a list box for example they are both shown as "0002281".  I need them to be shown as their original unique values, as they appear in the source database.  Is there a format/data type option etc that I can enforce on the field before running & reloading the script in qlikview to ensure the values are brought in as "002281" and "0002281"?

And before anyone suggests it No I can't alter the equipment codes, remove the leading zeros and make the values unique that way (don't know why they been entered like this, they just are.  And we have historical work orders against these so they have to stay as is!!!)

Thanks for any help

Dan

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

QlikView will automatically interpret these as numbers - hence it will see the number 2281 in both cases.

What you can do, is one of the two following:

  • Load the field using    Text([Equipment Code]) as [Equipment Code]
  • Load the field using    'ID: ' & [Equipment Code] as [Equipment Code]

HIC

View solution in original post

4 Replies
hic
Former Employee
Former Employee

QlikView will automatically interpret these as numbers - hence it will see the number 2281 in both cases.

What you can do, is one of the two following:

  • Load the field using    Text([Equipment Code]) as [Equipment Code]
  • Load the field using    'ID: ' & [Equipment Code] as [Equipment Code]

HIC

Peter_Cammaert
Partner - Champion III
Partner - Champion III

In the Preceding Load, use the text() function to load the values without numerical representation.

The QV symbol table works here against you. QlikView will store dimension values in the field symbol table as a dual value, compoosed of a numerical value and a string representation. For the two dimension values you mentioned, the numerical value is the same. the first value is stored alliright. After a symbol table lookup for the second value QlikView will discover that the value already exists (with a different string representation though, but that's not important). The stored one will prevail, and the second one will be thrown away.

For every numerical value, QlikView keeps only one string representation. The one you are getting is probably the one that is loaded first.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

In addition to what Henric posted, QV formats the numbers in that field according to the first record. So 0002281 is the first record and Qliview says, 'aha, a number, formatted like 0000000' and applies that rule to the next one too, so they both appear as '0002281'. Underneath they are both the number 2281, unless they are forced to text, as Henric explained.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks HIC

knew it'll be simple

followed your canonical date blog too, was really useful in helping us as we have 3-4 date fields in one table and needed means to view all values for given month, using one selection etc - thanks for that too !!!

Dan