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

    Selecting data

    kamarul halifi

      Hi,

       

      I got a problem when i want to select some data based on their ID. For example,

       

      2016-05-17 16_53_49-New Microsoft Excel Worksheet - Excel.png

       

      i have table for main group, group 1 and group 2. i want to count status (single) in main group but i just want the status (single) which belong to group 1. The result should show only the status (single) for group 1. Can anyone help me  to solve my problem.

      reddys310

      mto

      robert_mika

        • 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.