Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi scagha66,
You can try the below
Inquiries:
LOADidencuesta 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.
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
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.