topic Re: Linking 2 Tables with association key in QlikView

Linking 2 Tables with association key

sudhir0538 — Sat, 16 Nov 2024 20:32:22 GMT

Hello Experts !

I've 2 different tables,1 table is from Excel(from user) and another one from database.Build an 3 field associated key to make these two tables related. Problem is that in Excel one of the numeric field is stored as 12345 and in db the same is stored as 0000012345. the length in the db is not specific. and because of this i'm unable to make relationship and data model as well. For non numeric fields there's no problem.

Kindly help how to get these problems solved.

Re: Linking 2 Tables with association key

marcus_sommer — Tue, 02 Jul 2019 12:24:31 GMT

I think you need to enforce a numeric interpretation of the values, maybe with something like: num('0000012345').

- Marcus

Re: Linking 2 Tables with association key

sudhir0538 — Tue, 02 Jul 2019 12:26:36 GMT

Hi Marcus, Thanks for the reply. That field also contains alphanumeric data too.

Re: Linking 2 Tables with association key

marcus_sommer — Tue, 02 Jul 2019 12:32:48 GMT

Then you need to check the values and to branch into the appropriate measurement, maybe with something like:

if(isnum(FIELD), num(FIELD), FIELD) as FIELD

- Marcus

Re: Linking 2 Tables with association key

sudhir0538 — Wed, 03 Jul 2019 04:43:02 GMT

Hi Marcus,

I tried the same but it's not working. i thing the field in db is in text format may be?

Re: Linking 2 Tables with association key

marcus_sommer — Wed, 03 Jul 2019 06:28:34 GMT

My suggested approach to check for a numeric value and remove leading zero's worked within the UI. By loading from a database I'm not absolutely sure and you might need to apply real converting-functions, for example something like this:

alt(num(num#(Field)), num(num#(Field, '##########')), Field)

You may need to play a bit with it (maybe in multiple parallel statements to see what worked respectively the differences between them) to find (all) needed combinations. It should really work.

- Marcus