topic Re: retrieve the right date in QlikView

retrieve the right date

Thu, 04 Jun 2015 17:06:24 GMT

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

Re: retrieve the right date

sunny_talwar — Thu, 04 Jun 2015 17:16:27 GMT

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;

Re: retrieve the right date

pokassov — Thu, 04 Jun 2015 17:17:13 GMT

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;

Re: retrieve the right date

maxgro — Thu, 04 Jun 2015 17:23:55 GMT

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;

Re: retrieve the right date

sunny_talwar — Thu, 04 Jun 2015 17:26:11 GMT

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

Re: retrieve the right date

Wed, 10 Jun 2015 21:17:28 GMT

Thanks a lot sunindia

Re: retrieve the right date

Wed, 10 Jun 2015 21:18:18 GMT

thanks for your help

Re: retrieve the right date

Wed, 10 Jun 2015 21:18:55 GMT

thanks expert

Re: retrieve the right date

MarcoWedel — Fri, 19 Jun 2015 21:45:21 GMT

you are already ...