Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sudhir0538
Creator
Creator

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.

Labels (6)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

sudhir0538
Creator
Creator
Author

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Creator
Creator
Author

Hi Marcus, 

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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