join resident files comparing date fields

[Anonymous]

I have two files from different origens that I need to unite.

The first file is on a SQL server and is loaded like this:

Inquiries:

LOAD

idencuesta as "IDInquiry",

IDINDIC as "CODINDIC",

autonumber (Indicador & '|' & VAL) as "Cod_Indicador_Val",

INQUIRYDATE as "InquiryDate",

The other one is on an AS400 database and is loaded like this:

Objetivos:

LOAD ID as "id_objetive",

IDINDIC as "CODINDIC",

VALIDDATE as "ValidDate",

I need to find the id_objective with the right date in relation to the inquiry date.

The VALIDDATE has to be smaller or equal to the INQUIRYDATE.

It should be something like this:

load *

resident DatosEncuestas:;

inner join

load

id_objetivo

resident Objetivos

where FechaVigencia = (select max(FechaVigencia) from resident ObjetivosB where FechaVigenciaB <= FechaEncuesta);

This is just an idea that I know doesn't work, but I really have no idea how to handle this in qlikview.

Any ideas?

[#2]

Hi scagha66,

You can try the below

Inquiries:

LOAD

idencuesta as "IDInquiry",

IDINDIC as "CODINDIC",

autonumber (Indicador & '|' & VAL) as "Cod_Indicador_Val",

INQUIRYDATE as "InquiryDate",

Join

LOAD ID as "id_objetive",

IDINDIC as "CODINDIC",

VALIDDATE as "ValidDate",

After This

Inquiries2:

Load *,

'1' as Junk,

resident Inquiries where ValidDate <= InquiryDate ;

drop table Inquiries;

With the above one you are taking only the ids where ValidDate is lessthan or equal to Inquiry Date .

Hope this solves your Problem .

Thanks & Regards,

Chakravarthy.

[Anonymous]

Hi Chakravarthy.

thanks for your answer, but is solves only part of the problem. I need to have only the nearest or equal VALIDDATE. With your solution I could have a numberr of records where VALIDDATE is less than INQUIRYDATE.

thanks anyway

scagha66

[#4]

Hi scagha66,

I am not clear about your Nearest date . Using the above you can get the less than & equal date and if you want the nearest date you can add a And condition and solve your problem .

Regards,

Chakravarthy.