5 Replies Latest reply: Jul 22, 2016 12:31 PM by Sunny Talwar RSS

    Add more fields to load statement!!!

    Nicholas Colino

      Hello all,

       

      I am trying to add more fields to a load statement with 2 max functions already embedded. I have the field [KE Task ID] which is the key however I want to bring in more fields from the same QVD called, [RC Version Date], [RC Project Name], and [RC Project Number].

       

      However whenever I add these fields directly to the load statement it interferes with the max functions and I get errors, as soon as I remove the fields from the load statement, it runs flawlessly.

       

      Here is the code that runs flawlessly but doesn't have all the fields I need.

      LoadJoin:

      LOAD *

      FROM [\\ke_root.qvd] (qvd);

      Left Join

      LOAD *

      FROM [\\ke_task.qvd] (qvd);

      Left Join (LoadJoin)

      LOAD [KE Task ID],

          Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

          Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

      FROM

      [\\ke_task_work_info.qvd] (qvd)

      Group By [KE Task ID];


      Here is the code that is preferred but does not run without errors...

      LoadJoin:

      LOAD *

      FROM [\\ke_root.qvd] (qvd);

      Left Join

      LOAD *

      FROM [\\ke_task.qvd] (qvd);

      Left Join (LoadJoin)

      LOAD [KE Task ID],

            [RC Version Date],

            [RC Project Name],

            [RC Project Number],

           Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

           Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

      FROM

      [\\ke_task_work_info.qvd] (qvd)

      Group By [KE Task ID];

       

      Thanks,

      Nick

        • Re: Add more fields to load statement!!!
          Sunny Talwar

          May be this:

           

          LoadJoin:

          LOAD *

          FROM [\\ke_root.qvd] (qvd);

          Left Join

          LOAD *

          FROM [\\ke_task.qvd] (qvd);

          Left Join (LoadJoin)

          LOAD [KE Task ID],

                [RC Version Date],

                [RC Project Name],

                [RC Project Number],

              Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

              Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

          FROM

          [\\ke_task_work_info.qvd] (qvd)

          Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

            • Re: Add more fields to load statement!!!
              Nicholas Colino

              Hi Sunny,

               

              I have tried your method and it worked well for me, but I ended up getting 3 duplicates.

               

              I was supposed to have 1992 records, after employing your method I got back 1995 records. How can I fix this?

                • Re: Add more fields to load statement!!!
                  Sunny Talwar

                  I am not sure what might be the reason for this duplication. But you can try this approach:

                   

                  LoadJoin:

                  LOAD *

                  FROM [\\ke_root.qvd] (qvd);

                  Left Join

                  LOAD *

                  FROM [\\ke_task.qvd] (qvd);

                   

                  MappingTable1:

                  Mapping

                  LOAD [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number] as Key1,

                            [Last KE Task Work Info Date Q5];

                  LOAD [KE Task ID],

                        [RC Version Date],

                        [RC Project Name],

                        [RC Project Number],

                      Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5]

                  FROM

                  [\\ke_task_work_info.qvd] (qvd)

                  Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];


                  MappingTable2:

                  Mapping

                  LOAD [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number] as Key2,

                            [Last KE Task Work Info Mod Date Q5];

                  LOAD [KE Task ID],

                        [RC Version Date],

                        [RC Project Name],

                        [RC Project Number],

                      Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

                  FROM

                  [\\ke_task_work_info.qvd] (qvd)

                  Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

                   

                  And then may be this:

                   

                  FinalLoad:

                  LOAD *,

                            ApplyMap('MappingTable1', [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number]) as  [Last KE Task Work Info Date Q5],

                            ApplyMap('MappingTable2', [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number]) as  [Last KE Task Work Info Mod Date Q5]

                  Resident LoadJoin;

                   

                  DROP Table LoadJoin;

                   

                  The approach is outlined here:Don't join - use Applymap instead