topic Re: Flagging Complimentary Dates in App Development

Flagging Complimentary Dates

m_perreault — Mon, 11 Jun 2018 17:04:53 GMT

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

Re: Flagging Complimentary Dates

sunny_talwar — Mon, 11 Jun 2018 17:17:47 GMT

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

Re: Flagging Complimentary Dates

m_perreault — Mon, 11 Jun 2018 18:14:26 GMT

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?

Re: Flagging Complimentary Dates

sunny_talwar — Mon, 11 Jun 2018 18:33:06 GMT

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;

Re: Flagging Complimentary Dates

m_perreault — Mon, 11 Jun 2018 18:49:20 GMT

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

Re: Flagging Complimentary Dates

sunny_talwar — Mon, 11 Jun 2018 18:55:07 GMT

May be I missed a parenthesis

If(IDNumber = Previous(IDNumber),

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