0 Replies Latest reply: Feb 21, 2018 3:51 AM by Cameron Vine RSS

    Join table at a group level

    Cameron Vine

      I am attempting to join a table with some new information at the group level not the granular level.

       

      I am using left join and have managed to get the data in but it is not behaving as expected.

       

      Expectation

      Capture.PNG

      Result with code

      Capture.PNG

      Code

      LIB CONNECT TO 'SLA TotalCount SiT';

       

       

      RestConnectorMasterTable:

      SQL SELECT

          "businessDate",

          "sourceSystemVersion",

          "reportName",

          "containerName",

          "totalCount"

      FROM JSON (wrap on) "root"

      WITH CONNECTION(

                      QUERY "FromDate" "$(vFromDate)",

                      QUERY "ToDate" "$(vToDate)"

          );

       

       

      [Count Table]:

      LEFT JOIN ([SLA Detail])

      LOAD

      Date(Date#(Left([businessDate],10), 'YYYY-MM-DD'), 'YYYYMMDD') AS [Date],

      //     [sourceSystemVersion],

          [reportName] as [Report],

          [containerName] AS [ContainerName],

          sum([totalCount]) AS [Total Count FeedID],

          'Source' AS [System]

       

       

      RESIDENT RestConnectorMasterTable group by Date(Date#(Left([businessDate],10), 'YYYY-MM-DD'), 'YYYYMMDD'), containerName, reportName;

       

       

      [Count Table]: NoConcatenate

      LOAD *

      Resident [SLA Detail];

       

       

      Drop Table [SLA Detail];

      Rename Table  [Count Table] to [SLA Detail];

      Drop Field [ContainerName] From [SLA Detail];

       

       

      DROP TABLE RestConnectorMasterTable;

       

       

      LIB CONNECT TO 'SLA TotalCount SiT';