compare the values of 2 tables

[Fanch]

Hi everybody, 

Hope you are doing well 😎

I load in my script Two tables, one with the id of the last mont and the other with the id of the current month like this :

I want to create two tables on my script with the id that is removed and the other with the ID that is appears with a result like this :

I have tried many functions (Lookup, Applymap ...) without a good result 🙄

Can you help me ? How i can create this two tables OLD ID and NEW ID ?

Thanks a lot !

Fanch

[JuanGerardo]

Hi @Fanch , I recommend to use an outer join and then get what you want:

All:

Load

   ID_MONTH,

   True() AS FromLast

Resident ID_LAST_MONTH;  // Or from your datasource

Outer Join

Load

   ID_MONTH,

   True() AS FromNew

Resident ID_CURRENT_MONTH;  // Or from your datasource

Result:

Load

   ID_MONTH,

   If(FromLast and not FromNew, 'Old ID',

      If(FromNew and not FromLast, 'New ID', 'Both') AS Status

Resident All;

Drop Table All;

JG

[Fanch · Accepted solution]

Thanks a lot JuanGerardo, 

Very good exemple !!!! 💪