Link ID to the right value in Script

[#1]

Hi,

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

Table1
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

Table2
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

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?

[alexandros17 · Accepted solution]

Table1:

LOAD *  Inline

[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;

[#3]

It worked perfectly! Thanks!