2 Replies Latest reply: Mar 1, 2013 7:07 AM by dbuijsman RSS

    Link ID to the right value in Script

      Hi,

       

      Im using 2 tables from an oracle database. Lets take this example:

       

      Table1
      FCLYearAmount
      10002008200
      20002008150
      30002008600
      10002009300
      20002009125
      3000200975
      1000201067898
      20002010345
      30002010753

       

      Table2
      PER_IDFCLYearFromYearTill
      1100020072008
      2200020072008
      3300020072008
      2100020092009
      1200020092015
      3300020092009
      3100020102015
      1200020092015
      2300020102015

                                                                                      

      I want to link the PER_ID to AMOUNT, using FCL as a key. But only when the Year in table 1 matches or is in between YearFrom and YearTill.

       

      So for example if i click in my application on the year 2008 and PER_ID number 1 the pivot table or list box must show FCL number 1000.
      But if i click on the year 2009 and PER_ID number 1  the pivot table or list box must only show  FCL number 2000.

       

      I want to do this in my script and not with an expression in my application cause that messes up other things.

       

      Is this possible?

        • Re: Link ID to the right value in Script
          Alessandro Saccone

          Table1:

           

           

           

           

          LOADInline

          [FCL ,Year ,Amount

          1000,2008,200

          2000,2008,150

          3000,2008,600

          1000,2009,300

          2000,2009,125

          3000,2009,75

          1000,2010,67898

          2000,2010,345

          3000,2010,753

          ]
          ;



          Outer Join



          Table2:

          LOAD * Inline

          [PER_ID, FCL ,YearFrom, YearTill

          1,1000,2007,2008

          2,2000,2007,2008

          3,3000,2007,2008

          2,1000,2009,2009

          1,2000,2009,2015

          3,3000,2009,2009

          3,1000,2010,2015

          1,2000,2009,2015

          2,3000,2010,2015

          ]
          ;



          CCC:

          NoConcatenate

          LOAD * Resident Table1

          where Year >= YearFrom and Year <= YearTill;

                                                                                          

          DROP Table Table1;