60 columns comparison mapping problem

[jlito]

Hello guys,


I need some help or information for this logic.

I have one table (say Table A1) that has 60 columns containing date field.

I have a driver table that has also a date (Table B1)

Now I need to get the minimum of the date in Table A which is greater than the date in Table B.

The same thing another field will be populated but now I need the date that is equal or less than the date in Table B.

Have you already encountered such implementation in Informatica without using a stored procedure. When we run using connected stored procedure the performance suddenly drop to 4 records. Usually, the throughput is 100 rows/sec.

Thanks.

rgds,
jlito

 

[blom0344]

a possible strategy:

1. Do a lookup to table B to fetch the date
2. Pass only records from A that have date greater than date from B
3. Apply a ranking on a variable that stores something like:
(3000000 - TO_DATE (date, 'J'))
This will give rank 1 to lowest date from the filtered set
4. Pass only the row with rank = 1




T. Blom
Information analyst
tbl@shimano-eu.com

 

[jlito]

Thanks. But what do you mean by passing to a lookup. I have a problem on doing a lookup on the 60 columns of the record (I'm comparing these 60 columns with a specified date in Table A). I need to know which date in the 60 columns should i take.

Do you have any suggestion for this kind of logic?


Regards,
jlito

 

[blom0344]

Sorry, I misunderstood your question......

I suppose you need to do this for whole set of records from table B for a certain dimension?

I can think of a bit laborous solution like this:

1. Normalize table 1 like:

DIM date1 date2 date3 date4 date5 ..............

to:

DIM 1 date1
DIM 2 date2
DIM 3 date3
DIM 4 date4
DIM 5 date5
...............
...............

Store this data in a temp table

2. Create a mapping with temp table as source, do a lookup to table A for each value of DIM.
Calculate the DATEDIFF between the date from normalized table and the lookup. Use a filter to get rid of the dates that have negative DATEDIFF. The remaing records are fed through an aggregator with group by dim and min(DATE_DIFF)

Result is minimum from table A but greater than date from table B.

Other approaches may work even better, but by normalizing table A (having data in rows instead of columns) you can use standard aggregate functions like MIN(....)

Create a batch on these two mappings and you have a 2 step solution!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Sorry, I misunderstood your question......

I suppose you need to do this for whole set of records from table B for a certain dimension?

I can think of a bit laborous solution like this:

1. Normalize table 1 like:

DIM date1 date2 date3 date4 date5 ..............

to:

DIM 1 date1
DIM 2 date2
DIM 3 date3
DIM 4 date4
DIM 5 date5
...............
...............

Store this data in a temp table

2. Create a mapping with temp table as source, do a lookup to table A for each value of DIM.
Calculate the DATEDIFF between the date from normalized table and the lookup. Use a filter to get rid of the dates that have negative DATEDIFF. The remaing records are fed through an aggregator with group by dim and min(DATE_DIFF)

Result is minimum from table A but greater than date from table B.

Other approaches may work even better, but by normalizing table A (having data in rows instead of columns) you can use standard aggregate functions like MIN(....)

Create a batch on these two mappings and you have a 2 step solution!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jlito]

Now i can start my design. thanks man.

rgds,
jlito