Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
Contributor

Join table at a group level

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