Arriving at the difference in dates across Files in IDEA

Hello Group Members,

Let's say you would like to identify the delay in settlement of customer product replacement claims on an online merchandising portal.

File A contains the Date of Claim and the Due Date for Replacement. File B contains the Actual Date of Replacement. The common field which binds both the files is the Invoice Number and Order Number.

To arrive at the difference in the Actual Date of Replacement with the Due Date for Replacement you need both the dates in one single file.

The best fit feature in IDEA to use in this scenario is Join under Analysis and Relate within IDEA.

Join brings data sets from diverse files together for comparison on the basis of one or more matching key fields.

So here we Join File A with File B on the matching key fields Invoice Number and Order Number.

By using Matches Only within Join you get both the Due Date for Replacement and Actual Date of Replacement fields in one single IDEA file.

Now you can append a virtual numeric field in the joined file with the criteria @age(Actual Date of Replacement, Due Date for Replacement) to get the differences in the two dates.

Any difference in negative indicates the replacement was done prior to the due date which is a positive customer centric measure. Any positive difference needs to be reviewed for delays of 'x' days and more as per the internal policy of the merchandising site.

Regards

Group Admin Team