7 Replies Latest reply: Jul 4, 2017 6:08 AM by Bala Bhaskar RSS

    Subqueries in Qlik Sense

    Sarah Mghames

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

       

        • Re: Subqueries in Qlik Sense
          Ali Hijazi

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

          group by SIGNEDOFFBYUSER

            • Re: Subqueries in Qlik Sense
              Sarah Mghames

              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"

                • Re: Subqueries in Qlik Sense
                  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"

              • Re: Subqueries in Qlik Sense
                Sarah Mghames

                Any Help on the above?

                • Re: Subqueries in Qlik Sense
                  Bala Bhaskar

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