3 Replies Latest reply: May 20, 2015 12:46 PM by Sunny Talwar RSS

    Where not exists script

    Yvonne Han

      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

       

        • 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
              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:

              Capture.PNG


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

               

              HTH

               

              Best,

              Sunny

                • Re: Where not exists script
                  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:


                  Capture.PNG