Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table looks like this. There's a hierarchy structure here where the AcctParent is the parent of AcctChild. What I'm trying to do is to load the lowest level AcctChild, meaning if a AcctChild # appears in the AcctParent column, for example (the highlighted row) I want to exclude that row. I think this can be done by using a where not exists script like " where not exists (AcctChild, AcctParent)" but not sure how to write it exactly.
Can some advice on that?
Thanks
AcctChild | AcctName | AcctParent |
50000000 | COST OF SALES | FPL12A0000 |
51000000 | MANUFACTURING COST | 50000000 |
51100000 | MANUFACTURING COST | 51000000 |
51110000 | MANUFACTURING COST | 51100000 |
51111000 | MANUFACTURING COST | 51110000 |
51111001 | MFG COST - BOOKS | 51111000 |
51111002 | MFG COST - USE | 51111000 |
51111003 | MFG COST - ISE | 51111000 |
51111004 | MFG COST - TRANSLATION & ADAPTATION | 51111000 |
51111005 | MFG COST - ORIGINALS | 51111000 |
51111006 | MFG COST - OTHER | 51111000 |
51111007 | MFG COST - AGENCIES | 51111000 |
51111008 | MFG COST - COSA | 51111000 |
51111009 | MFG COST | 51111000 |
51111011 | MFG COST - 2 | 51111000 |
Hopefully I am following - you want to only load a row if the AcctChild is never referenced as a parent? I would do a second pass over a temp table, something like this:
//Source data temp table - Replace with your actual source
DataTemp:
LOAD * INLINE [
AcctChild, AcctName, AcctParent
50000000, COST OF SALES, FPL12A0000
51000000, MANUFACTURING COST, 50000000
51100000, MANUFACTURING COST, 51000000
51110000, MANUFACTURING COST, 51100000
51111000, MANUFACTURING COST, 51110000
51111001, MFG COST - BOOKS, 51111000
51111002, MFG COST - USE, 51111000
51111003, MFG COST - ISE, 51111000
51111004, MFG COST - TRANSLATION & ADAPTATION, 51111000
51111005, MFG COST - ORIGINALS, 51111000
51111006, MFG COST - OTHER, 51111000
51111007, MFG COST - AGENCIES, 51111000
51111008, MFG COST - COSA, 51111000
51111009, MFG COST, 51111000
51111011, MFG COST - 2, 51111000
];
//Apply filter
Data:
NOCONCATENATE LOAD * Resident DataTemp WHERE NOT EXISTS (AcctParent, AcctChild);
//Drop temp table
DROP Table DataTemp;
Hopefully I am following - you want to only load a row if the AcctChild is never referenced as a parent? I would do a second pass over a temp table, something like this:
//Source data temp table - Replace with your actual source
DataTemp:
LOAD * INLINE [
AcctChild, AcctName, AcctParent
50000000, COST OF SALES, FPL12A0000
51000000, MANUFACTURING COST, 50000000
51100000, MANUFACTURING COST, 51000000
51110000, MANUFACTURING COST, 51100000
51111000, MANUFACTURING COST, 51110000
51111001, MFG COST - BOOKS, 51111000
51111002, MFG COST - USE, 51111000
51111003, MFG COST - ISE, 51111000
51111004, MFG COST - TRANSLATION & ADAPTATION, 51111000
51111005, MFG COST - ORIGINALS, 51111000
51111006, MFG COST - OTHER, 51111000
51111007, MFG COST - AGENCIES, 51111000
51111008, MFG COST - COSA, 51111000
51111009, MFG COST, 51111000
51111011, MFG COST - 2, 51111000
];
//Apply filter
Data:
NOCONCATENATE LOAD * Resident DataTemp WHERE NOT EXISTS (AcctParent, AcctChild);
//Drop temp table
DROP Table DataTemp;
or this may be:
DataTemp:
LOAD * INLINE [
AcctChild, AcctName, AcctParent
50000000, COST OF SALES, FPL12A0000
51000000, MANUFACTURING COST, 50000000
51100000, MANUFACTURING COST, 51000000
51110000, MANUFACTURING COST, 51100000
51111000, MANUFACTURING COST, 51110000
51111001, MFG COST - BOOKS, 51111000
51111002, MFG COST - USE, 51111000
51111003, MFG COST - ISE, 51111000
51111004, MFG COST - TRANSLATION & ADAPTATION, 51111000
51111005, MFG COST - ORIGINALS, 51111000
51111006, MFG COST - OTHER, 51111000
51111007, MFG COST - AGENCIES, 51111000
51111008, MFG COST - COSA, 51111000
51111009, MFG COST, 51111000
51111011, MFG COST - 2, 51111000
];
Data:
NOCONCATENATE
LOAD FirstValue(AcctChild) as AcctChild,
LastValue(AcctParent) as AcctParent,
AcctName
Resident DataTemp
Group By AcctName;
DROP Table DataTemp;
Output:
I used the script that ckipling gave and made some modifications here.
HTH
Best,
Sunny
I think I did the opposite (Changes in RED)
DataTemp:
LOAD * INLINE [
AcctChild, AcctName, AcctParent
50000000, COST OF SALES, FPL12A0000
51000000, MANUFACTURING COST, 50000000
51100000, MANUFACTURING COST, 51000000
51110000, MANUFACTURING COST, 51100000
51111000, MANUFACTURING COST, 51110000
51111001, MFG COST - BOOKS, 51111000
51111002, MFG COST - USE, 51111000
51111003, MFG COST - ISE, 51111000
51111004, MFG COST - TRANSLATION & ADAPTATION, 51111000
51111005, MFG COST - ORIGINALS, 51111000
51111006, MFG COST - OTHER, 51111000
51111007, MFG COST - AGENCIES, 51111000
51111008, MFG COST - COSA, 51111000
51111009, MFG COST, 51111000
51111011, MFG COST - 2, 51111000
];
Data:
NOCONCATENATE
LOAD LastValue(AcctChild) as AcctChild,
FirstValue(AcctParent) as AcctParent,
AcctName
Resident DataTemp
Group By AcctName;
DROP Table DataTemp;
Output: