Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
sjmghames
Partner - Contributor II
Partner - Contributor II

Subqueries in Qlik Sense

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

7 Replies
ali_hijazi
Partner - Master
Partner - Master

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

group by SIGNEDOFFBYUSER

I can walk on water when it freezes
sjmghames
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

Any Help on the above?

ali_hijazi
Partner - Master
Partner - Master

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

I can walk on water when it freezes
scotly-victor
Creator II
Creator II

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
Partner - Master
Partner - Master

what is the error message that you are getting?

I can walk on water when it freezes
balabhaskarqlik

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