10 Replies Latest reply: Oct 19, 2016 8:07 PM by Supriya R RSS

    Using set analysis inside loop in Load editor in qlik

    Supriya R

      Hi,

       

      I have a requirement where i need to get counts of Invitation according to date and ClientID, for every client. Here how could i put or approach to solve this?

       

      I usually in front end for particular client i put expression like this

       

      SUM({$< clientID = {'15'}>}Invitation)

       

      But i need to get for all clients present in Client table by putting in Load Editor.

       

      How could i do this? Any kind of help , suggestion are Appreciated

       

      Regards,

        • Re: Using set analysis inside loop in Load editor in qlik
          MARCO HADIYANTO

          Hi Supriya

           

          You can try like this.

           

          Load Date,

                    ClientID,

          sum(Invitation) as Total_Invitation

          From xxxx

          group by Date, ClientID;

           

          Regards

            • Re: Using set analysis inside loop in Load editor in qlik
              Supriya R

              HI,

              Thanks for reply

               

              But invitation data's will be in invitation table

              Invitation Table

              invitationID  batchID CampaignID DateCreated

              2                     23             3             10/19/2016

              3                     23             3             10/01/2016

              4                     24             6            10/02/2016

              5                     24             5             10/04/2016

              6                     25             4             10/05/2016

              7                     23             3            10/07/2016

               

              ClientTable

               

              Client ID BatchID

              2                23

              3                24

              2                25

              3                27

               

              the logic should be such a way that it has to get invitation count for each client by batchID in invitation Table.

              LIke for client ID 2 invitation count should be 3 where datecreated is is less than today. Like this i need to create a virtual table with two columns of client ID and invitationcount for all clients.

                • Re: Using set analysis inside loop in Load editor in qlik
                  Jason Coggon

                  Hi Supriya,

                   

                  Then the following will give you your desired result:

                   

                  Total_Invitations:

                  LOAD batchID,

                             DateCreated,

                             COUNT(invitationID) AS Total_Invitations

                  Resident Invitation Table

                  Group By batchID,DateCreated;

                   

                  Then in your front end, just sum Total_Invitations less than today.

                  • Re: Using set analysis inside loop in Load editor in qlik
                    jagan mohan rao appala

                    Hi Supriya,

                     

                    It is not possible to use set analysis in Scripting, the only way is to join the tables and use Group By.

                     

                    Invitation_Temp:

                    LOAD

                    invitationID, batchID, CampaignID, DateCreated

                    FROM Invitation;

                     

                    Left Join(Invitation_Temp)

                    LOAD

                    Client ID, BatchID

                    FROM Client;

                     

                    InvitationCount:

                    LOAD

                    ClientID,

                    DateCreate,

                    Count(InvitationID) AS InvCout

                    RESIDENT Invitation_Temp;

                     

                    DROP TABLE Invitation_Temp;

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                      • Re: Using set analysis inside loop in Load editor in qlik
                        Supriya R

                        Hi,

                         

                        I am actually trying to achieve this kind of virtual table in script

                         

                        ClientID  BatchID   Type_of_Count             CurrentMonth_Count                   PreviousMonth_Count

                           15           23         Invitation                    (Total_Counts of invitation)             (Total_Counts of invitation)

                        15               23         Signup                        (Total_Counts of Signup)             (Total_Counts of Signup)    

                        15               23         Course_Completion    (Total_Counts of Completion)             (Total_Counts of Completion) 

                        15              24         Invitation                    (Total_Counts of invitation)             (Total_Counts of invitation)

                        15               24         Signup                        (Total_Counts of Signup)             (Total_Counts of Signup)   

                        15               24         Course_Completion    (Total_Counts of Completion)             (Total_Counts of Completion) 


                        Like above for all clients and respective Batches


                        For invitation count - i have to take date_created count  from Invitation Table

                        For Signup count - i have to take date_signed count  from Signup Table

                        For Course_Completion count - i have to take date_completed count and final_test_statue = passed from Course_CompletionTable.


                        I have attached three tables data.


                        According to replied answer i tried below code, but gives me data load error, and tell me to correct error



                        Table1:

                        LOAD `invitation_id` as sample_invitation_id,

                            `user_id` as sample_user_id,

                            `client_id` as sample_client_id,

                            `batch_meta_data_id` as sample_batch_meta_data_id,

                             date(floor(date_created),'MM-DD-YYYY') as sample_Date, 

                             `campaign_id` as sample_campaign_id;

                        SQL SELECT `invitation_id`,

                        `user_id`,

                            `client_id`,

                            `batch_meta_data_id`,

                            `date_created`,

                            `campaign_id`

                        FROM rcdbprod.INVITATION WHERE client_id in (6,12,13,15,16,3,8,10,18,19,20,21,22,23);

                         

                        Left Join(Table1)

                        LOAD

                            date(floor(date_accessed),'MM-DD-YYYY') as sample_date_accessed,

                            `batch_meta_data_id` as sample_batch_meta_data_id,

                           date(floor(date_signed),'MM-DD-YYYY') as sample_date_signed;

                        SQL SELECT

                            `date_signed`,

                            `batch_meta_data_id`,

                            date_accessed

                        FROM rcdbprod.SIGNUPACTIVITYLOG WHERE client_id in (6,12,13,15,16,3,8,10,18,19,20,21,22,23);

                         

                        InvitationCount:

                        LOAD

                        sample_client_id,

                        sample_batch_meta_data_id,

                        Count(sample_Date) AS InvCout,

                        Count(sample_date_signed) AS CompletnCout

                        RESIDENT Table1;

                         

                        DROP TABLE Table1;




                        Please help me on this.


                  • Re: Using set analysis inside loop in Load editor in qlik
                    dixit panchal

                    Hi,

                     

                    Try this function:

                    1) AGGR() function

                    2) Group By Function at script Level

                     

                    Using this function you can easily get desired output.

                     

                    Regards,

                    Dixit

                    • Re: Using set analysis inside loop in Load editor in qlik
                      Jason Coggon

                      Hi Supriya,

                       

                      You unfortunately cannot use Set Analysis in the Load Editor (Only for Front End Expressions), but you can try the following by creating a resident table that links back to your Fact table in the Load Editor.

                       

                      Total_Invitations:

                      LOAD ClientID,

                                 Date,

                                 SUM(Invitation) AS Total_Invitations

                      Resident Client_Table

                      Group By ClientID, Date;

                       

                      Kind Regards,

                       

                      Jason