Flagging Complimentary Dates

[m_perreault]

Hi All,

I have data that is formatted as below

ID's will have complimentary records in which the Open Date is equal to a Close Date of a previous record.  I would like to create a field to identify complimentary records so they can be sorted in correct order in a table.  Does anyone have any ideas on how to best do this?

Thanks,
Mark

[sunny_talwar]

Complimentary record? How will it look for the two rows you shared?

[m_perreault]

So what I mean by complimentary records is a record in which the CloseDate is the same as another records OpenDate.  So ideally these complimentary records will share a unique identifier. So for these two records there would be a new field 'RecordGroupingFlag' and these two records would have the same value.  Every set of complimentary records should have a unique identifier.  Does that make sense?

[sunny_talwar]

So, you might be able to do this in the script like this

LOAD If(IDNumber = Previous(IDNumber),

     If(OpenDate = Previous(CloseDate), Peek('SNo'), RangeSum(Peek('SNo'), 1), 1) as SNo

     ...

FROM ...

Order By IDNumber, OpenDate;

[m_perreault]

It doesn't seem to like the syntax with the RangeSum()

[sunny_talwar]

May be I missed a parenthesis

If(IDNumber = Previous(IDNumber),

     If(OpenDate = Previous(CloseDate), Peek('SNo'), RangeSum(Peek('SNo'), 1)), 1) as SNo