Complex Lookup

[daniel0623]

Dear all,

How to implement complex lookup in DSEE? Here is the description of problem:

I have 2 files: A and B. And I want to get file C through following arithmetic:

File A:
A1 A2
1 X
3 Y
5 Z

File B:
B1 B2 B3
0 2 U
2 4 V
4 6 W

Lookup condition: (A1 >= B1) and (A1 < B2)

Result file C:
C1(from A.A1) C2(from A.A2) C3(from B.B3)
1 X U
3 Y V
5 Z W


Pls help me implement it.Thanks a lot.

Daniel

[kumar_s]

Range lookup in PX
What is the volume of record you are dealing with.
If it is considerablly small, you can try to extapolate the records in an order.
Say
New File B:
BB1 B2 B3
0 U
1 U
2 V
3 V
4 W
..
..
And do a direct lookup.
Else you can build you own custom routine to acheive this.

[daniel0623]

If File B is very small, I can list all possibility of the Key.But the problem is Range is random and File B has about 20 million rows.I can't list.

[ray.wurlod]

PX isn't really geared to non equijoin lookups. None of the Lookup, Join or Merge stages supports any technique other than exact key match. The string_lookup_from_int32 function for the Modify stage also does an exact key match.

How are the source data stored? Possibly your best solution would be to load them into two database tables and perform the join with SQL.

[DSguru2B]

I feel the same. Load it into two temp tables and run a sql query on it. That would be the best, IMHO, solution.

[kumar_s]

You can find this function 'Range lookup' build in forth comming HAWK release.
No, no... I dont ask you upgrade just for this reason.