topic Re: Problems with text/num format in QlikView

Problems with text/num format

Tue, 01 Nov 2011 22:01:50 GMT

Hi, I have a problem with a application, and I don´t understand why.

I want to add a new table and create a key between the new table and an existing table, using a field productID, a 16 digit number, ex 1002344001022349.

Even though the exact same productID-numbers exists in both of the tables they don´t seem to connect.

My guess is that the format of productID is different in the two tables. But even if I try using num(productID) or text(productiD) it still doesn´t work.

Can anyone help me figure out why this is happening?

Thanks!

Problems with text/num format

swuehl — Tue, 01 Nov 2011 22:31:10 GMT

I remember some problems with large integer numbers not being recognized as numbers on load, but as strings. Can't find the thread at the moment.

What if you try (as an experiment for the moment) to force QV to treat your ID as a string by adding text to the field values, i.e. by using a concatenation

Load ...

productID & '-PID' as productID

...

then you should also check that the strings have the same length, maybe using len() function or trim() to remove spaces in front and at the end of your characters.

Could you upload a small sample here?

Regards,

Stefan

Re: Problems with text/num format

Tue, 01 Nov 2011 23:28:56 GMT

Thanks for your answer!

I tried concatenation without success.

As you can see below the format from the two tables looks a bit different.

PID2,4003111018841e+014

PID240031110188413

I also tried trim without success. When I used len(productID) the lengt is 15 for one table and 20 for the other.

//Hans

Problems with text/num format

swuehl — Tue, 01 Nov 2011 23:58:37 GMT

Have you tried

'PID' & num(productID,'0000000000000000') as productID

e.g. force a 16 digit integer formatting (BTW, I noticed that both PID numbers in your above post only show 15 digits?).

Re: Problems with text/num format

Wed, 02 Nov 2011 21:18:32 GMT

Hi, I just tried to do like that,

'PID' & num(productID,'0000000000000000') as productID

But unfortunately, still doesn´t work.

//Hans

Re: Problems with text/num format

Wed, 02 Nov 2011 21:24:58 GMT

Hans: do try putting num(ProductID) as ProductID with the preceding load.

I resolved a similar issue this way...hope it works for you too.