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

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

vish123 — Wed, 06 Apr 2022 15:23:02 GMT

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

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

Or — Wed, 06 Apr 2022 15:33:42 GMT

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.

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

vish123 — Wed, 06 Apr 2022 15:55:55 GMT

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

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

vish123 — Wed, 06 Apr 2022 15:59:53 GMT

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:

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

rwunderlich — Wed, 06 Apr 2022 16:16:13 GMT

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

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

Or — Wed, 06 Apr 2022 16:16:47 GMT

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.

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

vish123 — Thu, 07 Apr 2022 08:18:09 GMT

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.