Order the records based on Date Value

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 7:48 am

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

chulett · Post by **chulett** » Thu Jun 21, 2012 7:55 am

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

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 8:03 am

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...

chulett · Post by **chulett** » Thu Jun 21, 2012 8:17 am

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.

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 8:20 am

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...

ntr · Post by **ntr** » Thu Jun 21, 2012 8:32 am

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

chulett · Post by **chulett** » Thu Jun 21, 2012 8:33 am

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.

chulett · Post by **chulett** » Thu Jun 21, 2012 8:35 am

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.

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 8:39 am

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...

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 1:26 pm

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...

ray.wurlod · Post by **ray.wurlod** » Thu Jun 21, 2012 5:39 pm

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

Magesh_bala · Post by **Magesh_bala** » Thu Jun 21, 2012 5:42 pm

Thanks Ray!!!