NESTED Lookup

dataservices job design
1

Hi,

I have

Source A:
A.id
A.date

Lookup table B:
B.id
B.start
B.end

Condition for lookup :
IF (A.id = B.id and A.date >= B.start A.date <= B.end) = true the return A.id
ELSE
(A.id = B.id and A.date >= B.start -1 A.date <= B.end) = true then
return A.id
ELSE

(A.id = B.id and A.date >= B.start -2 A.date <= B.end) = true then
return A.id
ELSE

(A.id = B.id and A.date >= B.start -3 A.date <= B.end) = true then
return A.id

ELSE

(A.id = B.id and A.date >= B.start -4 A.date <= B.end) = true then
return A.id

Target Tbale C:
C.id (Will be populated with A.id as a result of lookup)

Please help

Thanks in Advance …

googli (BOB member since 2009-10-22)

2

The condition

(A.id = B.id and A.date >= B.start -4 and A.date <= B.end)

contains all other conditions already and all return A.id.

Werner Daehn :de: (BOB member since 2004-12-17)

3

It should pick B.start -1 if B.start is not available , same for others also
B.start -2 if B.start -1 not available…

Correct me if I am wrong

googli (BOB member since 2009-10-22)

4

Example

Source

A.id  A.date
1     1999.10.10

lookup

B.id  B.start     B.end
1     1999.10.10  2099.12.31
1     1999.10.08  2099.12.31

Conditions

1=1 and 1999.10.10 >= 1999.10.10 and 1999.10.10 <= 2099.12.31 ...... hence return 1
but at the same time the condition
1=1 and 1999.10.10 >= 1999.10.6(-4) and 1999.10.10 <= 2099.12.31 ...... hence return 1
is true as well

Hence there is no difference. There is something wrong in your conditions table.

Werner Daehn :de: (BOB member since 2004-12-17)