6 Replies Latest reply: Jul 29, 2016 2:47 PM by Settu Periyasamy RSS

    Help required

    Nitin Gupta

      Hi All,

       

      There is a table consisting of two fields, which gets updated two times a day.

      I need to get incremental logic implemented for the second time reload.

      See as per attached sample:

      A run will always be a full load.

       

      And in B run incremental will be applied.

       

      So For B run I just want the records which are either Inserted , Updated or deleted.

      Primary key can be [vehicle Code] &'-'&[Sales Code],

       

      As per sample my output after B Run Should be :

       

        

      Vehicle CodeSales Code
      BS1
      DS12
      DS11
      ES1
      ES3
        • Re: Help required
          Sudeep Mahapatra

          If I get time then I may create one but as of now I think you may use a field to identify the Reload time.

          since it will run two times a day so must be aware of the exact time the job runs. so if the reload time is already in today then it will be the second time and you can run the script for incremental load else it should be a full load from source table.

          • Re: Help required
            Robert Mika

            Do you want to keep  B S1 or B S2?

            B S2 was deleted...

              • Re: Help required
                Nitin Gupta

                HI Robert ,

                 

                I require

                B   S1

                 

                Because with these values only I will be able to do my further transformations.

                 

                Regards

                Nitin

                  • Re: Help required
                    Robert Mika

                    Req:

                     

                    So For B run I just want the records which are either Inserted , Updated or deleted.


                    B S2 has been deleted

                    B S1 remains

                     

                    but you are saying that you need to keep B S1.

                    Are they only 2 entry per Vehicle?

                    Why not keep A S1 then.?

                      • Re: Help required
                        Nitin Gupta

                        Hi Robert,

                         

                        I want the records which are Inserted or Updated,

                        And if the record from A run is deleted then all other combinations of Vehicle code and Sales Code should also be available for that specific Vehicle Code.

                         

                        No there may be several Sales Code for a particular Vehicle.

                         

                        So as B S2 is deleted , so S1 for B is also required.

                        and there was no change for A , so That entry is not required in b run

                          • Re: Help required
                            Settu Periyasamy

                            May be try this..

                            Directory;

                            //One Time Reload
                            //A:
                            //LOAD [Vehicle Code]&'-'&[Sales Code] as Key,     
                            //   [Vehicle Code],
                            //     [Sales Code]
                            //FROM
                            //[TEST (1).xlsx]
                            //(ooxml, embedded labels, table is [A RUN]);
                            //
                            //STORE A into A.qvd(qvd);
                            //
                            //DROP Table A;


                            A:
                            LOAD Key as temp_key,
                            [Vehicle Code],
                            [Sales Code]
                            FROM
                            A.qvd
                            (
                            qvd);


                            NoConcatenate

                            B:
                            LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
                            [Vehicle Code],
                            [Sales Code]
                            FROM
                            [TEST (1).xlsx]
                            (
                            ooxml, embedded labels, table is [B RUN]) Where not Exists(temp_key,[Vehicle Code]&'-'&[Sales Code]);

                            Concatenate(B)

                            LOAD temp_key as Key,
                            [Vehicle Code],
                            [Sales Code]
                            Resident A Where not Exists(Key,temp_key);

                            Inner Join(B)
                            LOAD [Vehicle Code]&'-'&[Sales Code] as Key,
                            [Vehicle Code],
                            [Sales Code]
                            FROM
                            [TEST (1).xlsx]
                            (
                            ooxml, embedded labels, table is [B RUN]);


                            DROP Table A;
                            EXIT Script;

                             

                            Capture1.JPG

                            Sample attached