Where not exists script

[Anonymous]

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

[#2 · Accepted solution]

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;

[sunny_talwar]

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

[sunny_talwar]

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: