Split string, get value from another table and then join string

[mibb1234]

I have a table similar to below (with other extra fields not shown for simplicity) and I need help with how to first split the string, then get the description from another table and then join the string again. 

I am trying to convert the Configuration code into descriptions as a new column.  The configuration code references multiple items and is separated by a ;

ID	Configuration Code
344	1;2;3
632	2;3;4
874	
521	4

This is the item table the each config code item references

Item	Description
1	Description 1
2	Description 2
3	Description 3
4	Description 4

This would be the expected output

ID	Config	Desired Output
344	1;2;3	Description 1; Description 2; Description 3
632	2;3;4	Description 2; Description 3; Description 4
874		
521	4;5	Description 4

Sometimes the config code can be null. If so then the description should also be null.

Sometimes an item may not exist in the 'Item' table. If so, it should just get the next description (last one row is an example - item 5 does not exist)

Any help or tips would be appreciated. 

Thanks

[MayilVahanan · Accepted solution]

Hi @mibb1234 

Try like below

Config:
LOAD ID, SubField([Configuration Code], ';') as [Configuration Code] INLINE [
ID, Configuration Code
344, 1;2;3
632, 2;3;4
874,
521, 4;5
];

Join(Config)
LOAD Item as [Configuration Code], Description INLINE [
Item, Description
1, Description 1
2, Description 2
3, Description 3
4, Description 4
];

Final:
NoConcatenate
Load ID, Concat([Configuration Code], ';') as [Configuration Code], Concat(Description, ';') as Description Resident Config
Group by ID;

DROP Table Config;

[mibb1234]

Thank you