topic Number IDs not linked properly. Challenge in QlikView

Number IDs not linked properly. Challenge

marcel_olmo — Sat, 16 Nov 2013 15:37:45 GMT

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.

Re: Number IDs not linked properly. Challenge

Clever_Anjos — Sat, 16 Nov 2013 15:46:36 GMT

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);

Re: Number IDs not linked properly. Challenge

swuehl — Sat, 16 Nov 2013 15:57:34 GMT

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).

Re: Number IDs not linked properly. Challenge

marcel_olmo — Sat, 16 Nov 2013 16:01:23 GMT

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.

Re: Number IDs not linked properly. Challenge

marcel_olmo — Sat, 16 Nov 2013 16:05:30 GMT

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

Re: Number IDs not linked properly. Challenge

swuehl — Sat, 16 Nov 2013 16:55:26 GMT

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.

Re: Number IDs not linked properly. Challenge

marcel_olmo — Sat, 16 Nov 2013 16:59:07 GMT

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.

Re: Number IDs not linked properly. Challenge

marcel_olmo — Sat, 16 Nov 2013 17:24:12 GMT

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.

Re: Number IDs not linked properly. Challenge

swuehl — Sat, 16 Nov 2013 18:29:36 GMT

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