topic Re: Where not exists script in QlikView

Where not exists script

Anonymous — Wed, 20 May 2015 16:09:26 GMT

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

Re: Where not exists script

Wed, 20 May 2015 16:22:26 GMT

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

sunny_talwar — Wed, 20 May 2015 16:43:03 GMT

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

Re: Where not exists script

sunny_talwar — Wed, 20 May 2015 16:45:55 GMT

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: