Join columns if records are missing and differ

[#1]

Hallo everybody,

I have a small issue- I would like to join two columns to get one market segment column. Data looks like that:

For some cases I don't have a market segment number (but market segment description is available, ex. Fish), while for others I just have a number (ex. 1200). Also there are some spelling mistakes in the Market segment column (ex. bread vs Bread). Additionally I have a list of all of the market segments numbers and their market segment description (excel file), which could be used. Does anybody have an idea how to join everything, so it makes sense and I finish with one market segment?

I suppose I need to link list of all of the market segments numbers with my data, giving the condition that if the number is not available, than use market segment description.

I would really appreciate your help, as I am trying to stop using Excel in such matters:)

Beata

[john_duffy · Accepted solution]

Hi Beata.

I have attached an application to provide a solution to your issue using joins and if statements in the load scripts. Basically, I attached the valid Market Segment Numbers and Market Segments to each row in the Market table. If available, I replaced the original value with the valid value.

Let me know if this helps.

John.

[#3]

Hallo John,

It looks indeed great! I am very busy right now to test it, but as soon as I will have more time to play with the data I will check if all of the results turn to be correct.

Thank you very much for your effort,

Beata