How to split a column which is having multiple values into Multiple rows using Wildmatch or any other logic in Qlik

[vish123]

I have a below scenario when i need to split the column values into multiple rows.

Rownumber , Indicator

1,                        MS Teams Slack Skype

2,                        Skype

3,                        Slack

4,                        MS Program Jabber

It should be transformed as below : Output

Rownumber , Indicator

1,                        MS Teams 

1,                        Slack

1,                        Skype

2,                        Skype

3,                        Slack

4,                        MS Program

4,                        Jabber

I cannot use subfield here because it will split "MS Teams" into "MS" , "Teams" so i need any other logic

[Or]

Assuming MS Teams is the only case where a space isn't the separator, you could replace 'MS Teams' with e.g. 'MS_Teams', then use Subfield, then replace MS_Teams with MS Teams again.

[vish123]

Hi Or,

Thanks for your reply.

The column many values with spaces like "MS Teams" . Is there a best way to do as i need to substitute space for all these words. Please suggest

[vish123]

Hi Or,

I have already tried above method. This is my code. Is there any way that i can give control to end user so that he can add these words which are having spaces so that i can map in my code.

Temp:
Load * Inline [
Rownumber, Indicator
1,MS Teams Skype Slack
2,Slack
3,Skype
4,MS Program Jabber
]
;

Table:
Load
Rownumber,
subfield(Column_clean,' ') as Column;
Load
Rownumber,
// pick(wildmatch(Indicator,'*MS Teams*','*MS Program*')+1,
// 'MS_Teams',
// 'MS_Program') as Column_clean

replace(replace(Indicator,'MS Teams','MS_Teams'),'MS Program','MS_Program') as Column_clean
Resident Temp;

Drop table Temp;

Output:

[rwunderlich · Accepted solution]

You could build a list of Indicators with spaces in an external file and use it with MapSubstring. Demonstrated here as an inline file. 

SpaceWords:
Mapping
Load Indicator, Replace(Indicator, ' ', '_')
Inline [
Indicator
MS Teams
MS Program
]
;
Temp:
Load * Inline [
Rownumber, Indicator
1,MS Teams Skype Slack
2,Slack
3,Skype
4,MS Program Jabber
]
;

Table:
Load
Rownumber,
Replace(subfield(MapSubString('SpaceWords', Indicator),' '), '_', ' ')  as Column
Resident Temp;

Drop table Temp; 

-Rob

[Or]

You could probably let your user fill those words into a file or database table, read that in your script, and then loop through them and replace each one individually. Very clunky, though, and will require a reload each time the source is changed.

[vish123]

Hi Rwunderlich,

Wonderful :), Thanks for the solution. This is helpful.

Hi Or,

Thank you too for your inputs/suggestions. I am marking this now as solution.