Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Complimentary record? How will it look for the two rows you shared?
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?
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;
It doesn't seem to like the syntax with the RangeSum()
May be I missed a parenthesis
If(IDNumber = Previous(IDNumber),
If(OpenDate = Previous(CloseDate), Peek('SNo'), RangeSum(Peek('SNo'), 1)), 1) as SNo