3 Replies Latest reply: Oct 14, 2016 3:48 AM by Nicola Zanetti RSS

    Connect 2 tables

    Nicola Zanetti

      Dear Gurus,

      I've the following problem:

      Two table with the following values:

      Table A:

        

      IDGroupFirst ID2Last ID2Descriprion
      70000SP330SP36006020000010602000001ALTRI CREDITI
      70000SP330SP36006020000150602000015ALTRI CREDITI
      70000SP330SP36006020000170602000017ALTRI CREDITI
      70000SP330SP36006020000200602000023ALTRI CREDITI
      90000SP35006040000020604000100EFFETTI ATTIVI IN PORTAFOGLIO
      95000SP33006040000020604000011CREDITI VERSO SOCIETA DI PROPRIETA'

       

      Table B:

      ID2Descriprion2Amount
      0602000001+1236,55
      0602000002+1244,00
      0602000003+123,56
      0602000004-125,15
      0602000005-12,18
      0602000006+13985,87
      06020000070
      06020000080
      06020000090
      06020000100
      06020000110
      06020000120
      06020000130
      06020000140
      06020000150
      06020000160
      06020000170
      06020000180
      06020000190
      06020000200
      06020000210
      06020000220
      06020000230
      06020000240

       

      End now the poroblem:

      In the table A i've only the first ID2 and the last ID2. Are there any way to connect this two tables?

      And the second problem is that ID2 can be contained in more than one row of Table A like the row 5 and 6.

       

      Sameone can help me?

       

      Thanks.

      Nicola.

        • Re: Connect 2 tables
          Andrew Walker

          Hi Nicola,

                              An IntervalMatch in your load script will do what you need. Add this after Tables A & B are loaded:

           

          IntervalMatch:

          IntervalMatch(ID2) LOAD [First ID2],[Last ID2] Resident TableA;

           

          This will give you a data model with a synthetic key. DON'T WORRY. Unexpected synthetic keys in your data model are often a sign of a defect in your script and they may cause performance problems. This is not the case when a synthetic key is created by the Intervalmatch function - this is normal and you need not add lines to your script to remove it. It's fine.

           

          This is the result shown in a tablebox:

           

          ID2 Descriprion2 Amount Group ID Descriprion
          0602000001+1236,55SP330SP36070000ALTRI CREDITI
          0602000002+1244,00  
          0602000003+123,56  
          0602000004-125,15  
          0602000005-12,18  
          0602000006+13985,87  
          06020000070  
          06020000080  
          06020000090  
          06020000100  
          06020000110  
          06020000120  
          06020000130  
          06020000140  
          06020000150SP330SP36070000ALTRI CREDITI
          06020000160  
          06020000170SP330SP36070000ALTRI CREDITI
          06020000180  
          06020000190  
          06020000200SP330SP36070000ALTRI CREDITI
          06020000210SP330SP36070000ALTRI CREDITI
          06020000220SP330SP36070000ALTRI CREDITI
          06020000230SP330SP36070000ALTRI CREDITI
          06020000240  
          SP33095000CREDITI VERSO SOCIETA DI PROPRIETA'
          SP35090000EFFETTI ATTIVI IN PORTAFOGLIO

           

          Kind regards

           

          Andrew

          • Re: Connect 2 tables
            Andrew Walker

            Hi Nicola,

                                Regarding the last question in your post:

             

            And the second problem is that ID2 can be contained in more than one row of Table A like the row 5 and 6.


            IDGroupFirst ID2Last ID2Descriprion
            90000SP35006040000020604000100EFFETTI ATTIVI IN PORTAFOGLIO
            95000SP33006040000020604000011CREDITI VERSO SOCIETA DI PROPRIETA'

             

            This is a problem with the source data I would suggest. If TableB has a record with ID2 in the range 0604000002 - 0604000011 it is not at all clear what the associated ID, Group or Description should be for such a record. Is it possible that [First ID2] for ID 90000 should read 0604000012 ?

             

            Kind regards

             

            Andrew