retrieve the right date

[#1]

Hello All,

I have two tables:

SENT :

LOAD *
inline [
REPORTING_ID, DATE_APPLI
2,                       04/05/2015
];

RECEIVED:
LOAD *
inline [
REPORTING_ID, AS_OF_DATE

2,                           03/05/2015
2,                            05/05/2015
2,                           10/06/2016 ];

and i have to retrieve  in the table received  the right date, knowing that  the right date is :

AS_OF_DATE > =DATE_APPLI

and i must retrieve the Is the closest AS_OF_DATE,  then, in the table RESULT  i  must have :

Results :

REPORTING_ID, DATE_APPLI, AS_OF_DATE

2,                   04/05/2015,         05/05/2015

I have not to retrieve 03/05/2015 because 03/05/2015 <  04/05/2015

I have not to retrieve 10/06/2016 because 05/05/2015  is  the closest

thanks in advance for your help

[sunny_talwar · Accepted solution]

Try this script:

SENT :

LOAD *

inline [

REPORTING_ID, DATE_APPLI

2,                      04/05/2015

];

Join (SENT)

LOAD *

inline [

REPORTING_ID, AS_OF_DATE

2,                          03/05/2015

2,                            05/05/2015

2,                          10/06/2016 ];

Join(SENT)

LOAD *,

  If(AS_OF_DATE - DATE_APPLI > 0, AS_OF_DATE - DATE_APPLI) as Difference

Resident SENT;

Right Join (SENT)

LOAD REPORTING_ID,

  Min(Difference) as Difference

Resident SENT

Group By REPORTING_ID;

[pokassov]

Hello!

SENT :

LOAD *

inline [

REPORTING_ID, DATE_APPLI

2,                       04/05/2015

];

RECEIVED:

LOAD *

inline [

REPORTING_ID, AS_OF_DATE

2,                           03/05/2015

2,                            05/05/2015

2,                           10/06/2016 ];

t1:

NoConcatenate

load *, 1 as tp, DATE_APPLI as DT Resident SENT;

Concatenate (t1)

LOAD *, 2 as tp, AS_OF_DATE as DT Resident RECEIVED;

t2:

load

  REPORTING_ID,

  DATE_APPLI,

  AS_OF_DATE

Where checked=1;

load

  REPORTING_ID, AS_OF_DATE,

  Previous(DATE_APPLI) as DATE_APPLI,

  if(Previous(tp)=1 and Previous(REPORTING_ID)=REPORTING_ID, 1,0) as checked

Resident t1

Order By

  REPORTING_ID,

  DT,

  tp;

DROP Tables t1, SENT, RECEIVED;

[maxgro]

SENT :

Mapping LOAD * inline [

REPORTING_ID, DATE_APPLI

2,                      04/05/2015

];

RECEIVED:

LOAD  REPORTING_ID, AS_OF_DATE, DATE_APPLI,

  if(AS_OF_DATE < DATE_APPLI, 9999999, AS_OF_DATE - DATE_APPLI) as DATE_DIFF;         // calc date difference

LOAD

  REPORTING_ID, AS_OF_DATE,

  ApplyMap('SENT', REPORTING_ID) as DATE_APPLI                                                                     // add DATE_APPLI

inline [

REPORTING_ID, AS_OF_DATE

2,                          03/05/2015

2,                            05/05/2015

2,                          10/06/2016 ];

// keep only the min date diff by reporting id

Right Keep (RECEIVED)

load

  REPORTING_ID,

  min(DATE_DIFF) as DATE_DIFF

Resident RECEIVED

group by REPORTING_ID;

[sunny_talwar]

I am glad we thought on the same lines. Makes me feel I can be an expert like you one day

[#6]

Thanks a lot sunindia

[#7]

thanks  for your help

[#8]

thanks expert

[MarcoWedel]

you are already ...