23 Replies Latest reply: Aug 24, 2011 1:31 AM by sandeepa rout RSS

    How to create a filter

    sandeepa rout

      Greetings,

       

       

      I am using Qlikview 10 personal edition.

       

      I would like to ask you how to create a filter in Qlikview.

       

      Actually I have created tow straight boxes there I have included wo labels one is "Matching_status' which is showing whether the record is "matched" or "unmatched" and another one is "service_status" which is showing whether it is "included" or "excluded".

       

      When I am creating a listbox and in general tab, in the field section when I am giving the expression as " if(x=y, 'Included', 'Excluded').. it is showing me two fields as Included, Excluded in the listbox.. But default is taking the first one and the second one is not functioning at all..

       

      Hope you are all getting my point.

       

      Please explain how to resolve it.

        • Re: How to create a filter
          sandeepa rout

          I mean to say it is only taking the True value not the false value. why don't understand.

            • Re: How to create a filter

              It is not completely clear whay you mean, can you post your report as an example?

               

              Regards,

               

              Bert

                • Re: How to create a filter
                  sandeepa rout

                  The report scenario is : There are straight tables one is Account another is contact..

                   

                  My task is comparing data from two datasources x and y.

                   

                  in Account straightable I have x.x1,y.x1, x.x2,y.x2 and so on...  Same here in Contact table.. I have added one label as "Matching Status"..

                  There I am comparing these data..

                   

                  But I have to filter these data so that they can see only matching or unmatching data.. So I have created one listbox.. But it is showing only the true value means only the matching data not the unmatching data..

                   

                   

                  Hope this explanation will help you to understand my query.

                   

                  Many Thanks

                  Sandeepa

                    • Re: How to create a filter

                      Hi Sandeepa,

                       

                      Can you post a snippet of your script where you determine the matching / unmatching part?

                       

                      Regards,

                       

                      Bert

                        • Re: How to create a filter
                          sandeepa rout

                          For contacts:

                          if(med_title=aspen_title and med_persontypename=aspen_persontypename and med_initials=aspen_initials and med_firstname=aspen_firstname and med_lastname=aspen_lastname and med_recordstatus=aspen_recordstatus and aspen_gender=med_gender and aspen_person_service=med_person_service, 'matched','unmatched')

                           

                          For Accounts:

                           

                          if(med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename and postal_street=med_postalstreet and shipping_street=med_shippingstreet ,'matched','unmatched')

                           

                          In listbox I am combining both.

                            • Re: How to create a filter
                              sandeepa rout

                              if((med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename and postal_street=med_postalstreet

                              and shipping_street=med_shippingstreet) and (med_title=aspen_title and med_persontypename=aspen_persontypename and med_initials=aspen_initials

                              and med_firstname=aspen_firstname and med_lastname=aspen_lastname and med_recordstatus=aspen_recordstatus and aspen_gender=med_gender

                              and aspen_person_service=med_person_service),'matched','unmatched')

                               

                               

                               

                              if(aspen_person_service=med_person_service, 'Included', 'Excluded')

                               

                               

                              This is the list box query

                               

                              I don't understand why it is showing only the True values..

                               

                              I am writing the expression in Generaltab->field->expression.

                                • Re: How to create a filter

                                  Hi Sandeepa,

                                   

                                  The logic in the listbox should be moved to the script. The resulting column should be shown in the listbox.

                                   

                                  Regards,

                                   

                                  Bert

                                    • Re: How to create a filter
                                      sandeepa rout

                                      Please explain in brief..

                                        • Re: How to create a filter

                                          Hi,

                                           

                                          You need to make sure that the unmatched definition is linked to the unmatched data.

                                          Something like this has to be created:

                                           

                                          Tables created earlier in script:

                                          Table A

                                               TableA.ID

                                               ....

                                               TableA.Matched

                                           

                                          Table B

                                               TableB.ID

                                               TableA.ID

                                               ....

                                               TableB.Matched    

                                           

                                          New script part:

                                          MatchLink:

                                          LOAD TableA.ID,

                                                    TableA.Matched

                                          resident TableA;

                                           

                                          left join (MatchLink)

                                          LOAD TableA.ID,

                                                    TableB.ID,

                                                    TableB.Matched

                                          resident TableB;

                                           

                                          join (MatchLink)

                                                TableA.ID,

                                                TableB.ID,

                                                if(TableA.Matched='Matched' and TableB.Matched='Matched', 'Matched', ' Not matched') as Matched

                                          resident MatchLink;

                                           

                                          drop field TableA.Matched, TableB.Matched from MatchLink;

                                           

                                          regards,

                                           

                                          Bert

                                        • Re: How to create a filter
                                          sandeepa rout

                                          Hello Bert,

                                           

                                          Please find attached of the file.

                                           

                                          By seeing this picture you will able to know that what exactly I want to explain you.

                                           

                                          You can see there are two straightbox tables.

                                           

                                          Account_Aspen vs Account_medpages in this table I am compiring data from two different data sources one is from aspen another is from medpages.

                                           

                                          Like wise in Contact_aspen vs Contact_medpages.

                                           

                                          Now I have to create flags which will filter only matched/unmatched/included/excluded.

                                           

                                          I have gone through the script which you have posted. But do you think for creating flag(filter) I need to write those script.

                                           

                                          If yes then how. Each table has data from two different datasources..

                                           

                                          And now I am totally confused and lost.

                                           

                                            • Re: How to create a filter

                                              See attached example report

                                                • Re: How to create a filter
                                                  sandeepa rout

                                                  Hi Bert,

                                                   

                                                   

                                                  Your example is Perfect.

                                                   

                                                  You are using "dataA" and "matchedA".But thing is that the "matchedA" field is defined by me.. Acually it's a label and I have written the expression for it.

                                                   

                                                  And it is not possible to define one by one which is matched or unmatched as there are billions of records.

                                                   

                                                  That's the problem why I can not able to do it.

                                                   

                                                  Any other solution is there.

                                                   

                                                   

                                                  Please have a look on that .bmp file which I have sent you.

                                                   

                                                  There you can able to see two straighboxes are created i-account_aspen vs account_med ii- contact_aspen vs contact_med.

                                                   

                                                  The first table contents are :

                                                  orgcode, aspen_orgname, med_orgname, aspen_orgstatusname, med_orgstatusname, aspen_orgtypename,med_orgtypename,postal_street, med_postalstreet, shipping_street, med_shippingstreet, Matching_status.

                                                   

                                                  Matching_status field is added by me actually it's a label and the expression is "

                                                  if(med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename

                                                  and postal_street=med_postalstreet and shipping_street=med_shippingstreet,'matched','unmatched')".

                                                  The fields in 2nd table :

                                                   

                                                  personcode, aspen_initial, med_initial, aspen_firstname, med_firstname, and so on....

                                                   

                                                  In second table service_status is a label and the expression is

                                                  if(aspen_person_service=med_person_service, 'Included', 'Excluded')

                                                  .

                                                   

                                                   

                                                  Now I have to add flags which will filter only matched/unmatched/excluded/included.

                                                   

                                                  Note: Aspen and medpages are two different datasources from which data are loading.

                                                   

                                                  May be this explanation will make you clear what I am trying to convey .