Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
leabe18
Contributor
Contributor

Allocate ID to another ID without duplicating

Hi, 
I want to be able to "Allocate" an ID to another ID based on a condition. 
My data is currently: 

Parent ID Type Status
P1 ID1 A Installed
P1 ID2 A Planned
P1 ID3 A Planned
P1 ID4 A Planned
P1 ID5 F Remove
P1 ID6 F Remove
P1 ID7 F Remove
P1 ID8 F Remove
P1 ID9 F Planned
P1 ID10 F Planned
P1 ID11 F Planned

 

I want to be able to "Allocate" the ID's of Type F where they match the status of a type A ID. Ensuring that a type F ID is not used more than once. 
The end result should look like this: 

Parent ID Type Status Allocated F ID
P1 ID1 A Installed  
P1 ID2 A Planned ID9
P1 ID3 A Planned ID10
P1 ID4 A Planned ID11


The part I am struggling with is ensuring that the F type ID is not used more than once. I end up with the same F Type ID "allocated" to all of the matching A type ID's.... (like the below - which is wrong)

Parent ID Type Status Allocated ID
P1 ID1 A Installed  
P1 ID2 A Planned ID9
P1 ID3 A Planned ID9
P1 ID4 A Planned ID9


Any advice please?

Labels (3)
1 Solution

Accepted Solutions
rubenmarin

Hi, I get that table with this script:

Orig:
LOAD * INLINE [
    Parent, ID, Type, Status
    P1, ID1, A, Installed
    P1, ID2, A, Planned
    P1, ID3, A, Planned
    P1, ID4, A, Planned
    P1, ID5, F, Remove
    P1, ID6, F, Remove
    P1, ID7, F, Remove
    P1, ID8, F, Remove
    P1, ID9, F, Planned
    P1, ID10, F, Planned
    P1, ID11, F, Planned
];

Combined:
NoConcatenate LOAD * Resident Orig Where Type='A';
Left Join LOAD Status, ID as AllocatedID Resident Orig Where Type='F';

DROP Table Orig;

Final:
NoConcatenate LOAD *, ID as UsedID, AllocatedID as UsedAllocatedID 
Resident Combined Where not Exists('UsedID',ID) and not Exists('UsedAllocatedID',AllocatedID)
Order By ID, AllocatedID;

DROP Table Combined;

View solution in original post

2 Replies
rubenmarin

Hi, I get that table with this script:

Orig:
LOAD * INLINE [
    Parent, ID, Type, Status
    P1, ID1, A, Installed
    P1, ID2, A, Planned
    P1, ID3, A, Planned
    P1, ID4, A, Planned
    P1, ID5, F, Remove
    P1, ID6, F, Remove
    P1, ID7, F, Remove
    P1, ID8, F, Remove
    P1, ID9, F, Planned
    P1, ID10, F, Planned
    P1, ID11, F, Planned
];

Combined:
NoConcatenate LOAD * Resident Orig Where Type='A';
Left Join LOAD Status, ID as AllocatedID Resident Orig Where Type='F';

DROP Table Orig;

Final:
NoConcatenate LOAD *, ID as UsedID, AllocatedID as UsedAllocatedID 
Resident Combined Where not Exists('UsedID',ID) and not Exists('UsedAllocatedID',AllocatedID)
Order By ID, AllocatedID;

DROP Table Combined;
leabe18
Contributor
Contributor
Author

that works, thank you so much