The reqirement is for a particular Acct_no if the there are multiple Rec_no and if the difference between rec_no is 1 then I need to take the record with lowest rec_no. Also the column Descript will have the concatenated value of all Descript column for the same Acct_no. Also if the difference between Rec_no is not 1 then that record should treated as new record.
So my output will look like
Acct_no|Rec_no|Descript
100|9|AACC --(Diff. between 10 and 9 is 1)
100|7|DD --(Diff. between 9 and 7 is not 1)
101|18|BBEEFF --(Diff. between 20,19 then 18 is 1)
102|30|GG --(Single record)
I have tried different logics in transformer using stage variables like checking old and new value etc but it is of no use.
Sort your data before your xfm on acct_no, rec_no (asc).
Within your stage variables whilst comparing keys - do a difference between previous_rec_no and present_rec_no and pass only records where the difference is <>1.
@Pandeesh
I need to check with the previous value.
@Prasson and Madhav
I need to apply this logic Acct_no wise so I need to take the acct_no in the stage variable too. Also I need to concatenate the value of Descript column with the same the diff. between previous and current Rec_no is 1(This can go upto n levels like for Acct_no 101 in my example). I need to take the row which is having lowest rec_no for a particular acct_no if the consecutive diff. is 1, not all the records.
I have tried similarly like sorting the data acct_no, rec_no wise and checking with previous value but I am still getting no success
Sorting your data and doing something similar to the if-then-else below will certainly ensure you pick the right record.. However concatenating it might be a little tricky.. does it matter in which order you concat the description ?
eg: for 100 - does it matter if its AACC or CCAA ?
@Madhav
Yes I need to concatenate in the correct order like here AACC only.
And I tried your logic; sorting on Hash partitioning on Acct_no asc, Rec_no Desc but I cannot just filter the data based upon
Apply filter condition to difference=1
because if you see my data then row#1 and row #4 should be considered as different rows. And my data will not pass the above filter.
You can add another stage variable to indicate if Acc No is changing between prev and curr record. Out put records from Transfomer only when there is a change in Acc No. Mean while for every record you can keep appending the text to Descript variable. This was you can concatenate values from any number of rows and sort based on first or last what ever RecNo you want. For rest, Prasoon's logic is fine.
Added another output from the tranformer that pulled across the concatenated desc value.. followed by a rd stage that retains the last record and then acts as a referential stream to the actual xfm output that lookups into this to get the DESC value...
chandra.shekhar@tcs.com wrote:@Madhav
Yes I need to concatenate in the correct order like here AACC only.
And I tried your logic; sorting on Hash partitioning on Acct_no asc, Rec_no Desc .
Without looking at the suggested answers too deeply, you do not want to partition on acct_no, rec_no as this will push different rec_no down different partitions. Only partition on acct_no to ensure all records with the same acct_no go down the same partition (and sort on acct/rec)
Added another output from the tranformer that pulled across the concatenated desc value..
How??
@Krypton
Thats what happening with me. When I use hash partitioning in the same transformer(Not using Sort stage) the above logic works for some records only and not for all