DSXchange

Hi All,

I have a scenario where I need to group the records based on the Order...

Input Records

ITM_INST Start Ts PTY_ID End Ts

123 1/1/2008 456 12/31/2008
123 1/1/2009 456 12/31/2009
123 1/1/2010 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 12/31/9999

In the above Input records I need to get three rows as Output...

I need to group rows 1 to 3 Since its same ITM_INST and PTY_ID...
and then row 4, 5 as as separate rec..

Output Should be

ITM_INST Start Ts PTY_ID End Ts

123 1/1/2008 456 12/31/2010
123 1/1/2011 567 12/31/2011
123 1/1/2012 456 9999

Please let me know how can I implement in Datastage.

Note: I'm using Datastage version 8.5

OK, I'll start... what have you tried? Anything involving an Aggregator perhaps?

Thanks Craig...
First I tried with Aggregator Stage using Min(Start date) and max(end date)
But it doesn't return correct values... I have a over lap

Second option using Stage Variable in Transformer by comparing prev row and current row I have rows
0
1
1
0
0
I'm not sure how to to split the records...

For the sample data you posted, the aggregation approach you mentioned is what I was going to suggest and should work fine... again, for the example you chose to post. Can you show us the input and output data that shows the "overlap"?

Show us your problem child.

Hi Criag,

Please find the output from Aggregator Stage...

ITM_INST Start Ts PTY_ID End Ts

123 1/1/2008 456 12/31/9999
123 1/1/2011 567 12/31/2011

When I group by ITM_INST and PTY_ID first 3 records and last records is considered as one group and 4th records as separate record....

I have overlap...

can you post what Exact input and what exact output do you want

You're right, didn't notice that the last group was the same as the first group so that aggregation while correct is not what you want.

For your stage variables, 0 means the record is the first record of a new group, the 1 means it is another record in the same/current group. If you have the 8.5+ version, you could use transformer looping to take information from both the 'first' and 'last' record in each group, understanding that they could be the same record.

ntr wrote:can you post what Exact input and what exact output do you want

They did, the first post shows that. The second example shows what they got instead of what they need when taking the aggregation route.

So We need to get the
0
1
1
Using the transformer stage by comparing the prev and Curr record and then do the Looping?

yes I have Datastage 8.5 Version...

I got the Logic to fix the issue...

PrevVal CurrVal
CurrVal 123:456

PrevAct CurrAct
CurrAct If Currval=PrevVal Then PrevAct Else PrevAct+1


Thanks Craig...

If you want to use Min and Max on dates in the Aggregator stage you have to set the Preserve Type property.

Thanks Ray!!!