check if value exists in tableB...

[#1]

Hi,

I have 2 excelfiles:

TableA:
- sector
- divisionnr
- division
- subdivision

TableB:
- divisionnr
- division
- new_divisionname
- new_subdivisionname

i'm using divisionnr&'-'&division as my key between tables. Every month i get TableA and needs to convert it to other names to make it userfriendly but i'm wondering if they add a new divisionnr or name in TableA, my TableB won't have a value for it. I need something to tell me if there are values in A that doesn't have a new_divisionname in TableB... i tried things like: if(isNull(new_divisionname),'UNKNOW FIELD',new_divisionname) as new_divisionname but i know that doesn't work cause it isn't null because of other values. I'm wondering if there's a way to set a flag or something??

pls help 🙂

[Miguel_Angel_Baeyens]

Hello,

You can use in the conditional the EXISTS() function:

Table:LOAD Field1, Field2FROM Source; Table2:LOAD NewField1, If(Not Exists(NewField1, Field1), 'Not in Field1', 'OK') AS NewField1Exists...

Hope that helps

[agilos_mla]

Hi,

I suggest to create mapping tables. This won't impac the associative model.

Map_B :

Mapping Load

division, 'OK'

From Table B;

Load

applymap('Map_B', division, 'KO') as Flag_Existing_in_B;

From Table A;

Then simply add a listbox on the Flag, OK/KO to filter the values that are presents in both tables or not.

Regards,

Michael