topic Re: Left join matching criteria (date interval) in QlikView

Left join matching criteria (date interval)

h_demarco — Thu, 25 Aug 2016 14:37:58 GMT

Dear gurus,

I am trying to left join 2 tables in order to add a column [Duração_parada] for each row in [Apontamentos], only when DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM

First of all, I load a table with PARADA data:


Parada:  
LOAD 'Parada' As Tipo_ap,  
  DATA_HORA_INI As DATA_HORA_INI_Parada,   
     DATA_HORA_FIM As DATA_HORA_FIM_Parada,   
     Num((DATA_HORA_FIM+Time('00:01:00')-DATA_HORA_INI)*24,'#######') As Duração_parada  
FROM QVDs\VIEW_PCPAPPARADA.qvd (qvd);

Then it loads Apontamentos:


Apontamentos:  
LOAD 'Produção' As Tipo_ap,  
     RECURSO,   
     DATA_HORA_INI,   
     DATA_HORA_FIM,   
     Num((DATA_HORA_FIM+Time('00:01:00')-DATA_HORA_INI)*24,'#######') As Duração,  
FROM QVDs\VIEW_PCPAPPRODUCAO.qvd (qvd);

And then it tries to join:


LEFT JOIN (Apontamentos)   
LOAD Sum(Duração_parada)  
Resident Parada  
Where DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM;

But it returns error:


Field not found - <DATA_HORA_INI>  
LEFT JOIN   
LOAD Sum(Duração_parada)  
Resident Parada  
Where DATA_HORA_INI<=DATA_HORA_INI_Parada AND DATA_HORA_FIM_Parada <=DATA_HORA_FIM

Any idea on how to achieve objective or handle error?

Re: Left join matching criteria (date interval)

settu_periasamy — Fri, 26 Aug 2016 15:23:54 GMT

Hi,

You have already aliased the field DATA_HORA_INI as DATA_HORA_INI_Parada,

So, if you are using resident load of Parada, the field wont be found

Re: Left join matching criteria (date interval)

h_demarco — Mon, 29 Aug 2016 11:33:46 GMT

Settu, thanks for your help. Understood your point.

My intention is to refer APONTAMENTOS.DATA_HORA_INI. Considering APONTAMENTOS was previously loaded (before join command), how can I do that?

On SQL command, this line would be something like:

ON APONTAMENTOS.DATA_HORA_INI <= PARADA.DATA_HORA_INI_Parada AND APONTAMENTOS.DATA_HORA_FIM >= PARADA.DATA_HORA_FIM_Parada

Thank you.

Henrique

Re: Left join matching criteria (date interval)

h_demarco — Fri, 14 Oct 2016 11:19:19 GMT

This error was solved using SQL. So Qlikview got fields ready from server.