2 Replies Latest reply: May 4, 2015 12:10 PM by Peter Cammaert RSS

    Cannot link my two tables properly

    Martijn Noorda

      Hello,

       

      I have written a script that makes an identifier by using the product of two nominal values. However, something seems to be going wrong and I don't know exactly what. Perhaps somebody can help me with my script.

       

      The first part of my script loads a table with two lines per OTC-derivative position, each position having a unique Trade ID and a USI (unique swap identifier). However, some positions are external, which causes that we do not have the USI of these positions nor the external trade ID. By using the product of the 2 nominals I want to link them to another report that includes these external positions.

       

      Firstly, I load the positions from a csv file. Then I use script to convert the table to a table where only one line remains per position, including the first nominal and the 2nd nominal. After having done that, I use the product of these 2 nominals to generate an identifier which I call [Identifier]

       

      Then is when the trouble starts

       

      I load a position report that should include the positions that I loaded firstly. This report has one line per position, with a nominal and a second nominal. I use the product of these nominals to generate the [Identifier] that I can use as a key field to link the two tables. However, I end up having a lot of rows containing the same values for all fields except for the identifier field, meaning I get a very large amount of of identifier fields that are linked to the same positions. Does anyone have a clue what might be the problem?

       

      I attached my script to this post.

        • Re: Cannot link my two tables properly
          Ramon Covarrubias

          this one of those cases where you really need to share the data in order for someone to able to provide a good advice

          • Re: Cannot link my two tables properly
            Peter Cammaert

            Many things can be wrong with this script. Especially when using numerical values as linkage fields.

             

            In each output table, create a copy of the Identifier field with a unique name. For example:

             

            Add [Nominal]*[NominalLeg2] AS [Identifier Hoofdtabel] to table Hoofdtabel (line 57) and

            add [nominal1] * [Nominal2] AS [Identifier Positietabelfx] to table Positietabelfx (line 78)

             

            Now create a table box with [Identifier Hoofdtabel], [Identifier] and [Identifier Positietabelfx] and compare the values that match/don't match. The association mismatches should be obvious.

             

            Best,

             

            Peter