Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jwaligora
Creator II
Creator II

Break up a field containing strings of tags into individual rows

Hi,

Data

ID, Tags
1, #soup #chicken #peas
2, 
3, #sandwich
4, #pasta #tomato #beef #basil
5, #beer

Desired output

ID, Tags
1, soup
1, chicken
1, peas
2,
3, sandwich
4, pasta
4, tomato
4, beef
4, basil
5, beer

What is the most efficient way of getting to the desired output?

I was thinking of getting the number of tags, then looping though, creating new rows. I'm worried I may be overcomplicating things, and that there might exist a function (or a combination of functions) to achieve this in one or two lines of code.

Thanks,
J.

Labels (2)
1 Solution

Accepted Solutions
MarcoWedel

 

LOAD ID,
     PurgeChar(SubField(Tags,' #'),'#') as Tags
Inline [
ID, Tags
1, #soup #chicken #peas
2, 
3, #sandwich
4, #pasta #tomato #beef #basil
5, #beer
];

 

View solution in original post

2 Replies
MarcoWedel

 

LOAD ID,
     PurgeChar(SubField(Tags,' #'),'#') as Tags
Inline [
ID, Tags
1, #soup #chicken #peas
2, 
3, #sandwich
4, #pasta #tomato #beef #basil
5, #beer
];

 

jwaligora
Creator II
Creator II
Author

I even looked at the subfield function but completely failed to grasp it would automagically act like this without me looping though and specifying the subfield number to extract. Thank you!