Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
LOAD DISTINCT SubField(IDs, ',') as [New ID List]
Resident mytable;
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
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
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
that is EXACTLY what I needed!
Thank you @rwunderlich 😁