A bit difficult requirement

[chandra.shekhar@tcs.com]

Hi All,

Sample Data

Code: Select all

ACCT RECNO TYPE NAME
100   11   1    Null
200   19   36   AA
200   20   1    Null
300   29   1    Null
100   10   36   BB
100   8    1    Null
300   27   36   CC
300   28   1    Null

Output Data

Code: Select all

ACCT RECNO TYPE NAME
200   20   36   AA
100   11   36   BB
300   28   36   CC

Conditions/Constraints
Records with TYPE = 36 will go in the o/p but the RECNO will be of the just previous(ACCT should be same) record. Previous means when RECNO is sorted desc, then the just bigger RECNO.

Kindly suggest

[chandra.shekhar@tcs.com]

just to add that I have tried a logic in the transformer where I partition the data on ACCT and sort on RECNO desc.
Now I also used stage variables where I check the old ACCT with new ACCT.
I am getting the data as I require except the RECNO is not coming correctly.
I have mentioned above that the RECNO will always be from the just previous record and the previous record means the next big RECNO.
I dont understand what/where am I missing the logic?

[chulett]

Show us your stage variables and their derivations.

[chetan.c]

Hi,

For the ACCT=100 the max recno is 11 and name is NULL not bb.
Any chance this is not being handled, to give the not null value causing the issue?

What is the RECNO that you are getting in the output for that ACCNT?

As craig said, do show us the stage variables.

Thanks,
Chetan.C

[chandra.shekhar@tcs.com]

@Chetan
As I told only record with TYPE = 36 will go in the o/p.
So for ACCT = 100, the RECNO will come from the just previous(larger) RECNO(in this case 10).
The NAME will always come from TYPE = 36(NOT TYPE = 1)

@Craig
In the transformer, partitioning is on ACCT with RECNO sorted desc.
stage variables are

Code: Select all

ACCT -> svNewAcc
TYPE -> svNewType
If svNewAcc = svOldAcc and svNewType = 36 Then 1 else 0 -> sv1
svNewAcc -> svOldAcc
svNewType -> svOldType

[romil_ds]

Hi Chandra,

Please try this.

ACCT -> svNewAcc
TYPE -> svNewType
RECNO -> svNewRec
If svNewAcc = svOldAcc and svNewType = 36 Then 1 else 0 -> sv1
If svNewAcc = svOldAcc and svNewType = 36 Then svoldRec else 0 -> sv2
svNewAcc -> svOldAcc
svNewType -> svOldType
svNewRec -> svoldRec


Put a constraint sv1 = 1 for output
and pass sv2 in the output RECNO column.

[chandra.shekhar@tcs.com]

Actually I figured it out.
Anyways thanx Romil for your help. :D