Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
that works, thank you so much