7 Replies Latest reply: May 18, 2016 4:50 AM by Sangram Reddy RSS

    S

    kamarul halifi

      Hi,

        • Re: Selecting data
          Sunny Talwar

          Create flags for each of the tables

           

          Group1:

          LOAD  *,

                    'Group1' as Flag

          FROM Source;

           

          Group2:

          LOAD *,

                    'Group2' as Flag

          FROM Source2;

           

          Now you can use set analysis to show stuff from Group1 using a set analysis like this:

           

          {<Flag = {'Group1'}>}

            • Re: Selecting data
              kamarul halifi

              Do you mean like this? Create flags for group1 and group2. Sorry i'm not clear what you said just now.

              2016-05-17 18_34_27-_aaa test _ Data load editor - Qlik Sense.png

                • Re: Selecting data
                  Sunny Talwar

                  Yes and no. The way to create the flag is right, but would script it a little differently. You pasted the script as image and hence won't be able to suggest any modifications

                    • Re: Selecting data
                      kamarul halifi

                      Here I attach the qvf file if you could suggest the modification. I'm sorry for not upload it earlier.

                        • Re: Selecting data
                          Sunny Talwar

                          May be something like this:

                           

                          MainGroup:

                          LOAD Id,

                               Name,

                               Status

                          FROM [lib://Desktop/Main group.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                           

                          Group1:

                          LOAD Id,

                               Name,

                               Status,

                               'Group1' as Flag

                          FROM [lib://Desktop/Group1.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                           

                          Concatenate(Group1)

                          LOAD

                              Id,

                              Name,

                              Status,

                              'Group2' as Flag

                          FROM [lib://Desktop/Group2.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                            • Re: Selecting data
                              kamarul halifi

                              I really appreciate your help. but i still not understand yet how to count status 'single' from main group which id belong to group1. The syntax that i want to create is like below :

                               

                              Count id(main group) =id(group1) where status='Single'.

                              Could you convert my syntax to qlik sense syntax.

                                • Re: Selecting data
                                  Sangram Reddy

                                  Hi Kamarul,

                                   

                                  I would suggest you to concatenate all the three table like this:

                                   

                                  MainGroup:

                                  LOAD Id,

                                       Name,

                                       Status,

                                       'Main' as Flag

                                  FROM [lib://Desktop/Main group.xlsx]

                                  (ooxml, embedded labels, table is Sheet1);


                                  Concatenate(MainGroup)

                                  Group1:

                                  LOAD Id,

                                       Name,

                                       Status,

                                       'Group1' as Flag

                                  FROM [lib://Desktop/Group1.xlsx]

                                  (ooxml, embedded labels, table is Sheet1);

                                   

                                  Concatenate(MainGroup)

                                  LOAD

                                      Id,

                                      Name,

                                      Status,

                                      'Group2' as Flag

                                  FROM [lib://Desktop/Group2.xlsx]

                                  (ooxml, embedded labels, table is Sheet1);


                                  Now if you use count([status]) as the expression and use two filters : Flag and ID, you will be able to acheive what you are looking for.


                                  Thanks,

                                  Sangram.