Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank you