13 Replies Latest reply: Jan 23, 2015 12:37 AM by O'Brian Newell RSS

    Resident load

    O'Brian Newell

      Hi,

       

      I have a quick question wonder if someone might be able to help.

      What I am trying to achieve is a simple filtering on data using information from two separate data tables in script as there are object loading issues due to the size of data. So:

       

      IF(fabs(Sum (Project_A.Cost)-Sum (Project_B.Cost)) >= 5000, 'ok', 'under') AS Project_Delta

       

      My script is as below:

       

      QUALIFY*;

      UNQUALIFY ID, Start_Date;

       

       

      Project_A:

      LOAD*,

      Right(Project_Description,4) AS ID;

       

      LOAD Start_Date,

           Project_Description,

           Cost,

           End_Date

      FROM Project_A.txt

      ;

       

      Project_B:

       

      LOAD*,

      Right(Project_Description,4) AS ID;

       

       

      LOAD Start_Date,

           Project_Description,

           Cost,

           End_Date

      FROM Project_B.txt

      ;

       

      How can I go about doing this in script?

      Is resident the approach and if so any ideas, would be welcome.

        • Re: Resident load
          anbu cheliyan

          Yes you have to use resident approach

           

          1. Project_A:
          2. LOAD*,
          3. Right(Project_Description,4) AS ID;
          4. .....
          5. Join(Project_A)
          6. //Project_B:
          7. LOAD*,
          8. Right(Project_Description,4) AS ID;
          9. ....
          10. Final:
          11. NoConcatenate
          12. Load *,IF(fabs(Sum (Project_A.Cost)-Sum (Project_B.Cost)) >= 5000, 'ok', 'under') AS Project_Delta
          13. Resident Project_A;
          14. Drop table Project_A;
            • Re: Resident load
              O'Brian Newell

              Thanks for your reply Anu.

               

              When I try this approach it returns field not found Project_B.Cost.

              Also I still want the data from both tables to be available

              • Re: Resident load
                Subba Reddy

                Can you attach the sample file.

                so that we can help you in better way.

                 

                @Sub2u

                  • Re: Resident load
                    O'Brian Newell

                    Hi,

                     

                    Sorry I am unable to attached due to the confidentiality of information.

                    • Re: Resident load
                      O'Brian Newell

                      Hi,

                       

                      See below an inline sample

                       

                      QUALIFY*;

                      UNQUALIFY ID, Start_Date;

                       

                       

                      Project_A:

                      LOAD*,

                      Right(Project_Description,4) AS ID;

                      LOAD * INLINE [

                          Start_Date, Project_Description, Cost, End_Date

                          01/09/2014, Business_Analysis, "10,000", 02/02/2015

                          01/10/2014, Implementation, "25,000", 02/04/2015

                      ];

                       

                       

                      Project_B:

                      LOAD*,

                      Right(Project_Description,4) AS ID;

                      LOAD * INLINE [

                          Start_Date, Project_Description, Cost, End_Date

                          01/09/2014, Business_Analysis, "15,000", 02/02/2015

                          01/10/2014, Implementation, "27,000", 02/04/2015

                      ];

                        • Re: Resident load
                          Jonathan Dienst

                          Hi

                           

                          Is this what you need?

                           

                          //QUALIFY*;

                          //UNQUALIFY ID, Start_Date;

                           

                          T_Projects:

                          LOAD

                            Right(Project_Description,4) AS ID,

                            Start_Date,

                            Project_Description As A.Project_Description,

                            Cost As A.Cost,

                            End_Date As A.End_Date

                          ;

                          LOAD * INLINE [

                              Start_Date, Project_Description, Cost, End_Date

                              01/09/2014, Business_Analysis, "10,000", 02/02/2015

                              01/10/2014, Implementation, "25,000", 02/04/2015

                          ];

                           

                          Join(Projects)

                          LOAD

                            Right(Project_Description,4) AS ID;

                            Start_Date,

                            Project_Description As B.Project_Description,

                            Cost As B.Cost,

                            End_Date As B.End_Date

                          ;

                          LOAD * INLINE [

                              Start_Date, Project_Description, Cost, End_Date

                              01/09/2014, Business_Analysis, "15,000", 02/02/2015

                              01/10/2014, Implementation, "27,000", 02/04/2015

                          ];

                           

                          Projects:

                          NoConctanate

                          LOAD *,

                          Resident T_Projects

                          WHERE fabs(Sum (Project_A.Cost)-Sum (Project_B.Cost)) >= 5000;

                           

                          Drop Table T_Projects;

                           

                          If you need to filter the data before loading (or during the initial load), you will need to do this in the source system - for example a view or SQL query with a where clause.

                           

                          HTH

                          Jonathan