Subqueries in Qlik Sense

[sjmghames]

Hi,

Can someone assist in writing the below oracle query in Qlik Sense?

  Oracle:

select l.pclogs_id, l.userid, count(l2.signedoffbyuser)

  from tasoapplanpclogs l

  left join tasoapplanpclogs l2

  on l.userid = l2.signedoffbyuser

  group by l.pclogs_id,l.userid.

  I have tried the below but it did not work:

LOAD
    PCLOGS_ID,
    "USERID"
   
FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

left join

Load

SIGNEDOFFBYUSER as "USERID",
count(SIGNEDOFFBYUSER) as Count

FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

[ali_hijazi]

you forgot to put the group by clause in the second load

group by SIGNEDOFFBYUSER

[sjmghames]

Even with Group by it didn't not work:

LOAD
    PCLOGS_ID,
    "USERID"
   
FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

left join

Load

SIGNEDOFFBYUSER as "USERID",
count(SIGNEDOFFBYUSER) as Count

FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs1234.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

group by PCLOGS_ID,"USERID"

[sjmghames]

Any Help on the above?

[ali_hijazi]

table_name:

LOAD
    PCLOGS_ID,
    "USERID"
   
FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

left join(table_name)

Load

SIGNEDOFFBYUSER as "USERID",
count(SIGNEDOFFBYUSER) as Count

FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs1234.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

group by SIGNEDOFFBYUSER

[scotly-victor]

Hi,

Please Try This

Table1:

LOAD
    PCLOGS_ID,
    "USERID"
   
FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

left join

Load

SIGNEDOFFBYUSER as "USERID",

SIGNEDOFFBYUSER

FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs1234.qvd]
(qvd)WHERE not IsNull(SENDDOCUMENTTYPE);

Table2:

LOAD

    PCLOGS_ID,

    "USERID"

count (SIGNEDOFFBYUSER)  resident Table1

group by PCLOGS_ID,"USERID"

[ali_hijazi]

what is the error message that you are getting?

[BalaBhaskar_Qlik]

Try this:

ABC:

LOAD 

     PCLOGS_ID,
    "USERID"
FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd) WHERE not IsNull(SENDDOCUMENTTYPE);

left join (ABC)

CDE:

Load

     SIGNEDOFFBYUSER as "USERID",
     count(SIGNEDOFFBYUSER) as Count

FROM [lib://Transformed Data Folder (ncqlik_administrator)/TASOAPPlanPCLogs.qvd]
(qvd) WHERE not IsNull(SENDDOCUMENTTYPE);

Final:

LOAD 

     PCLOGS_ID,

    "USERID"

    count(SIGNEDOFFBYUSER) as Count

Resident ABC

     Group by PCLOGS_ID, "USERID"

     order by PCLOGS_ID, "USERID";