Load last 2 Entries

[mailravi.dw@gmail.com]

Hi

My source and target are csv file. It contains the data as follows.

CNO CNAME RISKRATINGS ENTRY_TIMESTAMP
100 , ABC , 2 , 2011-02-03 10:15
100 , ABC , 3 , 2011-02-02 10:10
100 , ABC , 1 , 2011-02-04 12:15
101 , XYZ , 2 , 2011-02-03 10:12
101 , XYZ , 3 , 2011-02-04 16:13

For each CNO i want last 2 records based on the ENTRY_TIMESTAMP. The result should be as follows.

CNO CNAME RISKRATINGS ENTRY_TIMESTAMP
100 , ABC , 2 , 2011-02-03 10:15
100 , ABC , 1 , 2011-02-04 12:15
101 , XYZ , 2 , 2011-02-03 10:12
101 , XYZ , 3 , 2011-02-04 16:13

Your inputs are greatly appreciated.

[chulett]

Usually easier to reverse the sort and take the first X records, you could always resort it back afterwards.

[mailravi.dw@gmail.com]

Thanks for your input. As per your suggestion, I will do sort based on CNO and ENTRY_TIMESTAMP in reverse order(Desending order). How to pick first "X" records for each CNO.

[vinothkumar]

While sorting in descending order add CreateKeyChange column in sort stage and then use remove duplicates using CNO and CreateKeyChange column value. I believe you will get only 2 entries.

[ray.wurlod]

Use Head stage for first record(s), Tail stage for last record(s).

[chulett]

Don't forget, we're talking first/last 2 entries per group here.

[mailravi.dw@gmail.com]

Yes, My main worry is how to pick either first/ last 2 records from each group "CNO". Could any one give some light on it.

[Ravi.K]

Define three Stage variables SV1,SV2 and SV3.

Initialize Values for Stage Variables:

SV2=$

Derivation for Stage variables:

SV1= InputCNO
SV2= If SV2='$' Then '1' Else (If SV3=SV1 Then SV2+1 Else '1')
SV3= InputCNO

Write a Constraint at Transformer level as follows.

SV2<3

There could be so many ways to do it. This is one way.

NOTE: you need to execute the transformer as "Sequential Mode"

[mailravi.dw@gmail.com]

It solved my problem.

[mansoor_nb]

It is not necessary to execute the transformer in the sequential mode. You have to maintain the same partition as the sort stage. It will work.
If the volume of data is high then executing the transformer in the sequential mode can be costly affair.

Thanks

[Ravi.K]

Yes, You are right.

[chulett]

While it may work, you really should be using meaningful stage variable names so your code is 'self documenting'. SV1, SV2 and SV3 aren't especially helpful in figuring out what they are accomplishing.

[Ravi.K]

I am revising the code with meaning full names for easy understandability.

Define three Stage variables CurrentVal,Counter and PreviousVal.

CurrentVal=> InputCNO
Counter=> If CurrentVal=PreviousVal Then Counter+1 Else 1
PreviousVal=> InputCNO

Write a Constraint at Transformer level as follows.

Counter<3

Thanks Craig making the forum more meaning full.

[chulett]

No problem... one last nitpick, however.

Best practice is a common prefix on stage variables so they are easily recognized when referenced elsewhere, much like you are all doing for job parameters and the like, yes?

I use "sv" so would do something more like this:

Code: Select all

svCurrentVal=> InputCNO 
svCounter=> If svCurrentVal=svPreviousVal Then svCounter+1 Else 1 
svPreviousVal=> InputCNO

[Ravi.K]

Noted.