Page 1 of 1
A bit difficult requirement
Posted: Wed Dec 12, 2012 5:05 am
by 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
Code: Select all
ACCT RECNO TYPE NAME
200 20 36 AA
100 11 36 BB
300 28 36 CC
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
Posted: Wed Dec 12, 2012 5:17 am
by 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?
Posted: Wed Dec 12, 2012 7:30 am
by chulett
Show us your stage variables and their derivations.
Posted: Wed Dec 12, 2012 8:05 am
by 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
Posted: Wed Dec 12, 2012 8:23 am
by 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
Posted: Thu Dec 13, 2012 5:40 am
by 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.
Posted: Thu Dec 13, 2012 6:13 am
by chandra.shekhar@tcs.com
Actually I figured it out.
Anyways thanx Romil for your help. :D