Left join matching criteria (date interval)

[h_demarco]

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?

[settu_periasamy]

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

[h_demarco]

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

[h_demarco · Accepted solution]

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