Join table at a group level

[Anonymous]

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

Result with code

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';