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