DSXchange

I have 5 rows in input table. and the format is like that,

Date. code, count
2014-12-10, FINANCE, 150
2014-12-10, MARKETING, 200
2014-12-10, HR, 300

Now, the source is a table so, in one day there possible wont be any counts for HR, but in target file we need HR with count 0.

In query I got it, but want to achieve this in datastage.My job design is like this-
ROW GENERATOR
|
ODBC- LOOK UP - SEQUENTIAL FILE
'
So, I am generating 5 rows in row generator, and using look up stage. It is not working this way. Any help how to achieve this.

Bascially if there is no HR in source table,though I want to achieve like following..
Date. code, count
2014-12-10, FINANCE, 150
2014-12-10, MARKETING, 200
2014-12-10, HR, 0

thanks.

If each CODE only occurs once in your data, then:

1. Row generator stage which creates your 5 possible CODE values with a count of 0
2. Source table stage to read your data
3. Funnel stage to funnel your two data streams together
4. Sort stage on CODE and descending count, keeping only unique records.

This way the MAXimum value is kept, if there is no CODE value in the source data, the row from the row generator is used, otherwise the record with the highest count is used.

For speed one could sort the two sources and use the sortmerge option in the funnel stage and dispense with the subsequent sort stage.

Thanks a lot..,.Worked perfectly..