Number IDs not linked properly. Challenge

[marcel_olmo]

Hi guys,

I have two tables : Table 1 and Table2.

Both tables have in common the same ID and the same value per that Id : 100100167766650000000.

But because of the lenght, in Qlikview the ID is showed as : 1,0010016776665e+020

It is supposed to be the same, but, if I load both tables they are not properly linked.

As you can see in the image below :

Anybody knows how to solve it? I've Attached the document if you want to take a look at it.

[Clever_Anjos]

Table1:
LOAD AutoNumber(%ID) as %ID,
    [%ID Table1],
    Date1,
    Date2
FROM
Table1.qvd
(qvd);


Table2:
LOAD AutoNumber(%ID) as %ID, 
    [%ID Table2],
    Field1,
    Field2
FROM
Table2.qvd
(qvd);

[swuehl]

I think QV just can't handle this amount of number precision needed.

Either round your numbers to some smaller precision:

round(%ID / pow(10,7)) as %ID

or read your IDs in as text (you probably need to do this when reading from your source systems, before creating the QVDs).

[marcel_olmo]

Thanks for the try Clever,

but I do that, it thinks that there are 8 different IDs, and the Id is always the same.

It is supposed to create a unique ID key.

[marcel_olmo]

Thanks swuehl you almost got it.

But the problem is that it is supposed to be always the same Id, but as you can see, the last one fails

[swuehl]

The internal numeric representation seems to be different for your %ID values. Are you really 100% sure that these %ID values are identical in your source system?

Anyway, I think you should handle these when reading in from your source, not after QV might already messed up the values stored into the QVD.

[marcel_olmo]

Yes Swuehl, I think you might be right.

I'm reading the following post talking about the same :

http://community.qlik.com/message/391464#391464

And yes, I'm completely sure in this case it's the same Id for all cases.

The problem is that I can't create the QVD because I have no access to the Database, I'm trying to solve that using only my QVDs.

Regards, Marcel.

[marcel_olmo · Accepted solution]

I got it from the elder post.

If you do this :

Div(%ID, 100000000)&Mod(%ID, 100000000)  as ID

You'll get your desired result.

Hope that helps to you as well if you find this in the future Swuehl,

Regards, Marcel.

[swuehl]

Seems like your losing the last significant digit doing so, i.e. the trailing digit '5'.