Linking 2 Tables with association key

[sudhir0538]

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.

[marcus_sommer]

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

- Marcus

[sudhir0538]

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

[marcus_sommer]

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]

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 · Accepted solution]

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