Should I use TextBetween, SubField or something else for unique field creation?

[data_RN]

Hello All, 

I am trying to figure out how to load this data to get a list of all the IDs listed out.

My IDs are grouped together with separating ','s and has some duplicates, but I would like to end with a list of each entry once "New ID List".

IDs	New ID  List
PJ-1, PJ-2, PJ-4	PJ-1
PJ-2,PJ-3, PJ-5	PJ-2
PJ-6	PJ-3
	PJ-4
	PJ-5
	PJ-6

Does anyone have a suggestion on the best way to do this?

Thanks as always!

[rwunderlich]

LOAD DISTINCT SubField(IDs, ',') as [New ID List]
Resident mytable;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[data_RN]

Thanks @rwunderlich  , indeed this works!  One follow-up question, is there anyway I can map this new field to the concatenated field? It would be great if I could use the new field [New ID List] to filter so that it would recognize that it contains the new ID.  For example, If I filtered on PJ-2, I would want to see both

IDs:

PJ-1, PJ-2, PJ-4

PJ-2,PJ-3, PJ-5

In other words, I want to also recognize that they are linked.  If I was able to clean it up in the source file, I would want it to look like this:

New ID       List IDs
PJ-1             PJ-1, PJ-2, PJ-4
PJ-2             PJ-1, PJ-2, PJ-4
PJ-2             PJ-2,PJ-3, PJ-5
PJ-3             PJ-2,PJ-3, PJ-5
PJ-4             PJ-1, PJ-2, PJ-4
PJ-5             PJ-2,PJ-3, PJ-5
PJ-6             PJ-6

[rwunderlich · Accepted solution]

Just include the original field in your new table. That field will link back to the other table. 

NewList:
LOAD DISTINCT
  IDs, 
  SubField(IDs, ',') as [New ID List]
Resident mytable;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[data_RN]

that is EXACTLY what I needed!

Thank you @rwunderlich  😁