Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
ivan_will
Partner - Creator II
Partner - Creator II

Extremely strange behaviour when concatenating two tables - ID=001 and ID=0000001 become the same!!?

Hi all,

Could you please tell me what will happens when execute the following script:

LOAD * INLINE [

    F1, f2

    00000001, one

];

LOAD * INLINE [

    F1, f2

    001, two

];

it should be the table:

F1, f2

00000001, one

001, two

but it isn't!!!!!!!!!!!

it is

F1, f2

00000001, one

00000001, two

WHY????

it is strange , very strange! if I use text(F1) it will be represent as a text, but I do not want to use it, what to do?!

3 Replies
tresesco
MVP
MVP

ultimately 00000001 and 001 are same when they are treated as number. The QV considers the first numeric format for same number. That is if you put the second table first you will get

F1, f1

001, two

001, one

Regards,

tresesco

ivan_will
Partner - Creator II
Partner - Creator II
Author

Ok, it is uncorrect from the QV, SQL makes it right.

Think it is a bug!!!

So how to escape the situation without putting everywhere text (ID) as ID ....

swuehl
MVP
MVP

Are you sure that your SQL server doesn't store the data as char or varchar and you're not calling the F1 data from two different table fields, possibly formated in different ways?

I am almost sure that you won't be able to create a table in SQL that stores the data in numerical format, but allows you to set a text representation differently for every given field value (like a SQL replacement for your QV concatenated table).

IMHO:

If the leading zeros are part of the value, they should be stored that way in the database/datamodel. (i.e. as text)

Regards,

Stefan