0

I was wondering if anyone had a good solution to trying lookup a value against a range of values in a fuzzy lookup transformation.

By "range of values" I mean either a date range such as start and end dates in the lookup table or comparison range such as a high and low value of house numbers compared to the exact house number in a record.

Or if there is solution how to solve fuzzy lookup against only valid values by condtion.

Iam using ssis 2008

Example:

My sales data example:

ID   Product    Quatity      date 
1    Product1   1            1.2.2015
2    Produc?t1  1            1.2.2015
3    Produt1_   1            10.7.2015

My price list

ID    Name      Price     ValidFrom     ValidTO
1     Product1  10        1.1.2015      28.2.2015
2     Product1  11        1.3.2015      null

My sales data are kind of dirty (sources are various) - that's why I want to use FUZZY lookup.

I want to fuzzky lookup againts my price list but only against valid prices.

So after lookup result should look like this

ID   Product    Quatity      date       Price
1    Product1   1            1.2.2015   10
2    Produc?t1  1            1.2.2015   10
3    Produt1_   1            10.7.2015  11

But if I use fuzzy I cannot specify the condition before I use fuzzy lookup.

Thanks in advance, Martin

pnuts
  • 58,317
  • 11
  • 87
  • 139
Martin Lučan
  • 51
  • 1
  • 4
  • Matt Mason, former head of the SSIS team, has a blog post on using a Merge Join to accomplish a range lookup. I have a pattern when I'll explode out the range to force it into an equality (instead of Begin=2015-05-01 End=2015-05-07, in my lookup I'd build out 2015-05-01, 2015-05-02, 2015-05-03..., 2015-05-07) Takes a bit of memory to get there but it simplifies the data flow – billinkc Aug 04 '15 at 16:00
  • But it solves only exact lookup :( – Martin Lučan Aug 04 '15 at 16:35
  • 1
    Perhaps a sample of your source data, target and how you expect it to work might provide some insight on your question – billinkc Aug 04 '15 at 16:38
  • I updated my question with sample data of data. – Martin Lučan Aug 05 '15 at 08:39
  • The data from your sales source, needs to be bounded within the price lists' date range and *then* have the fuzzy name match logic applied? – billinkc Aug 06 '15 at 14:05
  • I realised that best solution how to solve my problem is to create temporary table with only valid price list for my sales data. – Martin Lučan Aug 11 '15 at 15:34

0 Answers0