Hi Ivar,

Try to create a custom sql  instead of writing a calculated filed. Join them on the condition you mentioned. it will help to resolve the issue.

Thanks,

Tharashasank

Hi Ivar,

Hope this will solve your problem.

Thanks,

Pritesh

Hi Ivar

If the idea of writing the custom sql that Tharashasank suggests doesn't appeal for any reason then I think I have an alternative solution.

I may be missing something that makes this not work but could you create a calculated field in the "SalesCars" data that converts all old department codes to new department codes, keeping the new department code where that's what you have.

So, for example:

CASE [Department Code]

WHEN '2300' THEN 'AB0002'

WHEN 'XT001' THEN 'AB0004'

WHEN '25863' THEN 'AB0005'

WHEN 'XB00R' THEN 'AB0006'

WHEN 'XB99C' THEN 'AB0007'

WHEN '10100' THEN 'AB0009'

WHEN '10110' THEN 'AB0010'

WHEN '10102' THEN 'AB0011'

WHEN '10103' THEN 'AB0012'

WHEN 'XC003' THEN 'AB0014'

WHEN 'XC004' THEN 'AB0015'

WHEN 'XXX001' THEN 'AB0018'

ELSE [Department Code]

END

Then you can just link this calculated field to the new department code field in the HierarchyCars data set.

I'm assuming here that the list of old department codes is now fixed and there won't be any new old department codes (if that makes sense) as you get new and refreshed data.  If there will then maintaining the code might make this solution impractical.  Also, if the list of old department codes is extremely long then writing the formula for the calculated field might be too tedious to want to attempt.

Hope this helps

Best wishes

Anna

Hi Ivar,

Yes I mean to LEFT join SalesCars$Department_code to HierarchyCars$Old_department_code and then again for HierarchyCars$New_department_code.

This will get you the desired data.

Thanks,

Tharashasank