Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nsnybs21qv
New Contributor II

Where not exists script

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 

   

AcctChildAcctNameAcctParent
50000000COST OF SALESFPL12A0000
51000000MANUFACTURING COST50000000
51100000MANUFACTURING COST51000000
51110000MANUFACTURING COST51100000
51111000MANUFACTURING COST51110000
51111001MFG COST - BOOKS51111000
51111002MFG COST - USE51111000
51111003MFG COST - ISE51111000
51111004MFG COST - TRANSLATION & ADAPTATION51111000
51111005MFG COST - ORIGINALS51111000
51111006MFG COST - OTHER51111000
51111007MFG COST - AGENCIES51111000
51111008MFG COST - COSA51111000
51111009MFG COST51111000
51111011MFG COST - 251111000

1 Solution

Accepted Solutions
Not applicable

Re: Where not exists script

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;

3 Replies
Not applicable

Re: Where not exists script

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;

Re: Where not exists script

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:

Capture.PNG


I used the script that ckipling gave and made some modifications here.

HTH

Best,

Sunny

Re: Where not exists script

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:


Capture.PNG

Community Browser