6 Replies Latest reply: Aug 29, 2018 10:51 AM by Samik Shah RSS

    Conditional Load

    Samik Shah

      Hi,

       

      I am loading two set of data

       

       

      Table 1: Some list of Invoice and their Purchase Order (PO) number

      Table 2: A Purchase Order Record Master

       

      Table 1 is having limited (few hundred thousand) data but Table 2 as the name suggest is has massive data (more than few millions)

       

      So, when essentially, I am loading millions of excess record from Table 2 which is not reverent to me and just unnecessarily occupy space and make further computation complex.

       

      So Is there any way I can do limit the load from Table 2 to the PO's which are listed in Table 1?

       

      Any help will be appreciated as I am going mad in finding some solution in last couple of days.

       

      Thanks,

        • Re: Conditional Load
          Ramon van Delft

          Hi,

           

          If I understand you right, you should do the following:

           

          First load Table 1

           

          Table1:

          LOAD

          PO,

          Field2,

          Field3

          FROM source

           

          Then LOAD Table2 with a where clause:

           

          Table2:

          LOAD

          PO,

          Field2,

          Field3

          FROM source where exists (PO);

           

          This way table 2 will only load the PO's that are previously loaded (in Table1)

          • Re: Conditional Load
            Vishwarath Nagaraju

            May be this? Change the field names accordingly in your 2nd table where the fieldname in your table1 should have the values in fieldname of table2. I assumed and listed the field names in WHERE Exists condition. Hope you got it.

             

            Table1:

            LOAD InvoiceNum,

                       PurchaseOrderNumber

            .....

            FROM Tablename1;

             

            Table2:

            LOAD PurchaseOrderRecordMaster,

                       .....

            FROM Tablename2

            WHERE Exists(PurchaseOrderNumber, PurchaseOrderRecordMaster);

              • Re: Conditional Load
                Samik Shah

                Thank you Vishwarath!

                 

                But I have used manual naming of column so I think its not working form me.

                 

                So can you please provide solution of below

                 

                Table1:

                LOAD InvoiceNum,

                           @1 as [Invoice Number]

                          @2 as [Purchase Order Number]

                .....

                FROM Tablename1;

                 

                Table2:

                LOAD PurchaseOrderRecordMaster,

                          @1 as [Purchase Order Number]

                          @2 as [Plant Name]

                .....

                FROM Tablename2

                WHERE Exists(???, ???);

                 

                So what should I put where I have used "??" should it be the as name or the Column name (I.e. "@2")

              • Re: Conditional Load
                arjun rao

                Hi,

                Please check this.

                 

                Table1:

                LOAD * INLINE [

                InvoiceID,Purchase Order,Value1

                1,PO1,1

                2,PO2,2

                3,PO3,3

                4,PO4,4

                5,PO5,5

                6,PO6,6

                7,PO7,7

                8,PO8,8

                9,PO9,9

                10,PO10,10

                11,PO11,11

                12,PO12,12

                13,PO13,13];

                 

                 

                Table2:

                LOAD * INLINE [

                Purchase Order,Date,Values2

                PO1,1-Jan-18,1

                PO2,2-Jan-18,2

                PO3,3-Jan-18,3

                PO4,4-Jan-18,4

                PO5,5-Jan-18,5

                PO6,6-Jan-18,6

                PO7,7-Jan-18,7

                PO8,8-Jan-18,8

                PO9,9-Jan-18,9

                PO10,10-Jan-18,10

                PO11,11-Jan-18,11

                PO12,12-Jan-18,12

                PO13,13-Jan-18,13

                PO14,14-Jan-18,1

                PO15,15-Jan-18,2

                PO16,16-Jan-18,3

                PO17,17-Jan-18,4

                PO18,18-Jan-18,5

                PO19,19-Jan-18,6

                PO20,20-Jan-18,7

                PO21,21-Jan-18,8

                PO22,22-Jan-18,9

                PO23,23-Jan-18,10

                PO24,24-Jan-18,11

                PO25,25-Jan-18,12

                PO26,26-Jan-18,13

                PO27,27-Jan-18,11

                PO28,28-Jan-18,12

                PO29,29-Jan-18,13

                PO30,30-Jan-18,14]

                where Exists ([Purchase Order]);

                 

                Capture.PNG