Replace substring in a field with value separated by comma

[19iv1987]

Hi to everyone, I'm trying to achieve a solution for my problem..

I have a field (named for example event_list) that is structured with number separated by comma for example

133,212,43

12

155,166,123,145

....

In another table I have the corrisponding string value for each number:

Column a     Column b

12                    red

133                  blue

212                  yellow

43                    black

155                  grey

166                  white

123                  pink

145                  violet

....

I want to replace the event_list field with this one

blue,yellow, black

red

grey,white,pink,violet

Can  anyone help me?

Thank u in advance!!

Eva

[sunny_talwar]

Use MapSubString Function here

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/MappingFunctions/MapSu...

[sunny_talwar]

Sample:

MappingTable:

Mapping

LOAD * INLINE [

    ColA, ColB

    12, Red

    133, Blue

    212, Yellow

    43, Black

    155, Grey

    166, White

    123, Pink

    145, Violet

];

Fact:

LOAD *,

  MapSubString('MappingTable', Field) as NewField;

LOAD * Inline [

Field

133,212,43

12

155,166,123,145

] (delimiter is |);

[19iv1987]

Hi Sunny T thank u for your answer but my problem is that I have more than 255.000 record each one with different value of the event_list field.. How can I avoid to write down the inline statement?

[sunny_talwar · Accepted solution]

Inline was just an example. You can load this from any source (database, excel, csv, qvd...)

MappingTable:

Mapping

LOAD ColA,

     ColB

FROM Source....;

Fact:

LOAD *,

  MapSubString('MappingTable', Field) as NewField;

LOAD Field

FROM Source...;

[19iv1987]

Thank u so so so much!! It works!!!

[sunny_talwar]

Awesome!!! I would suggest you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny