Hash File lookup

[vskr72]

I have a situation like like this with 2 hash files that I am performing a lookup:

File 1:

DateCol
D1
D1
D2
D3
D4
D5.....

File 2
DtStrt DtEnd
D11 D12
D13 D14
D15 D16
D21 D22
D23 D24...

Where D1,D2,D3,D4 are the dates which come in the range between DtStrt and DtEnd.

All I have to do is see in which range does each date from file fall and then pick the corresponding Dtstrt date.

Eg., if D2 comes between D15 and D16, then pass D15 as output.

I tried using 2 has files and it not giving me the right result. Not getting the right result. Any help is appreciated.

Thanks in advance

Kumar

[dhiraj]

Hi Kumar,

Inorder to query a hash file using relational operators , You should use the Universe stage. Refer to the following post.
viewtopic.php?t=92083

And ideally to perform a look up based on the value retrieved from another look up you should use 2 transformers.perform the first look up (in your case look up from file1) in the first transformer pass the retrieved value as a column to the next transformer and do look up in the file2 using universe stage and userdefined query.

IHTH

Dhiraj

[davidnemirovsky]

Well ideally you could use 1 transformer and perform both lookups in the same transformer using 2 hashed files as reference inputs.

Note: The derivation for the 2nd lookup will show up as an error (in red) but the job will compile successfully and run. Try it out.

Does anyone know if this is something that is on the wish list? I suppose it's fairly minor though.

[roy]

Hi,
you can use also 1 of theese:
1. perform a lookup directly ti a DB stage and use a user defined sql implementing a between clause in your where part.
2. insert your values to a table, then use a sql join to unload a hash file with the original column from your source data as the key and the data you want from that join as the lookup value.

all was covered in the past do a search and you'll find the advantages and disadvantages for each.

IHTH,