Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

retrieve the right date

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

8 Replies
sunny_talwar

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
Specialist
Specialist

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
MVP
MVP

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

Not applicable
Author

Thanks a lot sunindia

Not applicable
Author

thanks  for your help

Not applicable
Author

thanks expert

MarcoWedel

you are already ...