topic Re: Split string, get value from another table and then join string in QlikView

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

mibb1234 — Tue, 27 Apr 2021 14:54:47 GMT

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

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

MayilVahanan — Tue, 27 Apr 2021 15:16:14 GMT

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;

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

mibb1234 — Wed, 28 Apr 2021 09:33:31 GMT

Thank you