8 Replies Latest reply: Apr 19, 2012 11:02 AM by Anne Duffy RSS

    Distinct compare 2 diff tables in load

      Hi Experts

       

      Please help :

       

      I wish to load a table from a Warehouse and a table from Excel.

      In the script I wish to compare a created field from the Warehouse and a created field from Excel, see script below :

       

      MatchTab: LOAD '00'&[Parent NSC]&num([Account Number],'00000000')&Value as AccountNumber, [Approved? N/Y] /*upper([Customer Name]) as [RCMT Customer Name]*/ FROM [S:\Business Support\Master Log Qlikview.xls] (biff, embedded labels, header is 1 lines, table is RCMT$) Where [Date received by RCMT] >'06/04/2012'; Join LOAD ACC_NO&text((limit_now)*-1) as WHAccount FROM $(vFileDate).qvd (qvd) Where Period_dte >'06/04/2012'; FinTable:      LOAD AccountNumber /*as QVAccount*/ ,           WHAccount /*as MatchWHAccount*/ ,           if(AccountNumber=WHAccount and [Approved? N/Y]='Yes', 'Yes', 'No') as Matched Resident MatchTab; DROP Table MatchTab;

       

      This should create a field called Matched where if I display as a List box I can tick "Yes" and show all the Matched,

       

      The Errors/Issues I am having are the following :

       

      1) In some instances the AccountNumber and WHAccount would be duplicated , see data below

       

      ACCOUNTNUMBER          WHACCOUNT

      123                                   123

      444                                   666

      555                                   777

      123                                   888

      456                                   999

       

      Using the Match as it is above, it will match the 123 WHACCOUNT with BOTH the ACCOUNTNUMBERS 123, so if I filter in Matched Yes on table AccountNumber I have a result of 2 , Yet on WHACCOUNT result of 1 . I wish a result of 1 on both !!! ( so only matches First instance!!) I have tried variations of distinct and I cant quite get it

       

      2) If I select "No" on the List box, the data sect remains the same, I would require to filter in all accounts that do not match

       

      3) By using the above method I am creating a table that is the Key between the 2 tables

      I need an additional Key of Region, If I rename from Table WHAccount field region_name to Region it will create a key with AccountNumber Table but it will be a syn key as I have already created keys. See viewer below

       

      Map Keys.bmp

       

      Guys Thanks a mill


      Any thoughts or suggestions on any the above would really be greatly appreciated !!

       

      A

        • Distinct compare 2 diff tables in load
          Jagan Nalla

          I think it is coming based on [Approved? N/Y] field also. You are validating Matched Field with this conditon also. If possible can you attach sample file and show me what you want exactly there.

            • Re: Distinct compare 2 diff tables in load

              Hi Jagan

               

              I have attached a sample file, I would have expected to have a Match of 2 on BOTH the log and the warehouse.

              Actually in this instance the {Approved? N/Y] does seem to be working !!

               

              I would also need to applymap and make region Key ( without creating a syn key) using the data from the Warehouse

               

              Sure let me know what you think ?

               

              Thanks

              A

                • Re: Distinct compare 2 diff tables in load

                  Also when I select No in Matched it does not filter the opposite values as in wither the 7 for Warehouse or the 6 for Log

                   

                  Thanks

                    • Re: Distinct compare 2 diff tables in load
                      Jagan Nalla

                      Untitled.png

                      It is working perfectly according to data only.

                       

                      We are creating expression if( accountnumber = whaccount and approved ='Y' ). If you see in the image the Warehouse table has only one record for "Yes" matched but, for log table has two records. Thats why it is showing the different count for both fields count. Then what is the issue here.

                        • Distinct compare 2 diff tables in load

                          Hi Jagan

                           

                          I wish for it only match the first instance - I have attached screen shots of the source data.

                           

                          This is a call off of account so in the Warehouse we may have 1 instance yet on the log we may have it twice, we need to identify where we have duplication on the log and only match the 1st option

                          Also, it does not filter in the Unmatched when "No" is selected

                           

                          Thanks

                          A

                           

                          Sourca data.bmp

                            • Re: Distinct compare 2 diff tables in load
                              Jagan Nalla

                              Ok change your code as below and see.

                               

                              Warehouse:

                              LOAD Date,

                                   Account,

                                   Customer,

                                   Account&text((Value)*-1) as WHAccount,

                                   Value,

                                   Region_area,

                                   recno()  as RecWH

                              FROM

                              [S:\Business Support\Quality Management\RCMT\Qlikview RCMT SQL\Qlikview Sample Doc\Back Up data.xls]

                              (biff, embedded labels, table is [Warehouse data$]);

                               

                              Map1:

                              Mapping

                              LOAD WHAccount,

                                  WHAccount as Flag

                              Resident Warehouse;

                               

                               

                              LOG:

                              LOAD [Close Date],

                                   Sort,

                                   [Account Number],

                                   [Customer Name],

                                   Amount,

                                   Sort&num([Account Number],'00000000')&Amount as AccountNumber,

                                   ApplyMap('Map1',Sort&num([Account Number],'00000000')&Amount,Null()) as WHAccount,

                                   [Approved? N/Y],

                                   Region,

                                  recno()  as RecID

                              FROM

                              [S:\Business Support\Quality Management\RCMT\Qlikview RCMT SQL\Qlikview Sample Doc\Back Up data.xls]

                              (biff, embedded labels, table is [Log Data$]);

                               

                              Res_LOG:

                              LOAD If([Approved? N/Y]='Y', 'Yes', 'No') as Matched,

                                  WHAccount

                              Resident LOG

                              Where Exists(WHAccount);

                                • Distinct compare 2 diff tables in load

                                  Hi Jagan

                                   

                                  When I select No on Matched it is filtering in Approved=No,

                                   

                                  It should filter in all Unmatched -- identified by Approved<> Yes AND WHAccount <> AccountNumber

                                   

                                  Also re matching only first instance of account , in excel what I would do is concatenate the account field with the order number ( using a =COUNTIF(A$2:A2,A2))

                                   

                                  What do you think ?

                                   

                                  AccountNumber
                                  aa1
                                  bb1
                                  aa2
                                  dd1
                                  ee1
                                  aa3
                                  bb2

                                   

                                   

                                  AccountNumberID
                                  aa1aa1
                                  bb1bb1
                                  aa2aa2
                                  dd1dd1
                                  ee1ee1
                                  aa3aa3
                                  bb2bb2
                            • Re: Distinct compare 2 diff tables in load
                              Jagan Nalla

                              To help you understand i'm attaching sample file. Please check it.