Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
sudhir0538
Contributor

Linking 2 Tables with association key

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Linking 2 Tables with association key

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

5 Replies
MVP & Luminary
MVP & Luminary

Re: Linking 2 Tables with association key

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

- Marcus

sudhir0538
Contributor

Re: Linking 2 Tables with association key

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

MVP & Luminary
MVP & Luminary

Re: Linking 2 Tables with association key

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

sudhir0538
Contributor

Re: Linking 2 Tables with association key

Hi Marcus, 

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

MVP & Luminary
MVP & Luminary

Re: Linking 2 Tables with association key

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