DI Studio - Lookup Greater and Less Than

[DavidCaliman]

Can I use the operators > and < in the Lookup transformation in DI Studio?

I need to find if a date is within a date range for a given key and to return only one record for each search.

[Patrick · Accepted solution]

To answer such questions it's often worth to look into the code DI Studio generates. The DI Lookup transformation generates a data step with a hash lookup - and a hash lookup works via exact key match.

The SQL Join transformation would allow for date ranges.

[DavidCaliman]

Tks Patrick!

I will then use the join.

The lookup always returns one record for each match found?

[LinusH]

Yes, the first one I believe if there are a multiple matches.

[DavidCaliman]

If I want to return only one record for each code below, which component should I use?

- Group by CD and rank by MAX(DATA)

Input:

cd dsc date

1  aaa  2013-01-31

1  bbb  2013-01-02

1 cccc  2013-01-21

2 xxxx  2013-01-12

2  yyyy  2013-01-15

Output:

cd dsc date

1  aaa  2013-01-31

2  yyyy  2013-01-15

[Patrick]

Untested - but with a SQL you would need something as below.

You could also use a Proc Sort and then a data step with "if.first cd" - but the data step would need to be user written code.

proc sql;

     create table want as

          select o.*

          from

               have o,

               (select cd, max(date) as max_date from have group by cd having max(date) as date) i

          where o.cd=i.cd and o.date=i.max_date

     ;

quit;

[Barnipaz]

Try this using the join node with just one input

proc sql;

     create table want as

          select o.*

          from

               have o,

          having date=max(date)

     ;

quit;

[Patrick]

The hash code generated doesn't use "multidata" so for multiple keys only the first one will be loaded into the hash - and that's then what you get via a look-up.

[DavidCaliman]

The rank transformation does not automatically calculate this?

Input:

cd dsc date

1  aaa  2013-01-31

1  bbb  2013-01-02

1 cccc  2013-01-21

2 xxxx  2013-01-12

2  yyyy  2013-01-15

Output:

cd dsc date

1  aaa  2013-01-31

2  yyyy  2013-01-15

[Patrick]

May be it does. Make sure you don't have ties.

and.. you should always start a new thread for a new question and mark an answered thread as answered. Else everything becomes a mix-up like here.

[DavidCaliman]

OK. I will start a new thread. Tks.