Replicating a join that is done in MS-SQL (T-SQL)

[bilalgunay]

Dear all, 

below is a code piece written in Microsoft SQL Server. It is (with SQL) a relatively simple join, but I wanted to ask for your help if and how this can be done in Qlik script. The data is in QVDs already. I just pushed it to SQL tables so that I can do this join. I can obviously create a view and then load that back to Qlik, but it would be only a workaround.

In the select there is a Duration calculated, with nested if's. I can do that part.

If more info needed, do not hesitate to comment. Thanks in advance.

FROM TABLE_J as J
left join TABLE_C as C
on convert(date, C.[Date]) >= convert(date,J.[History_Start_Time])
and convert(date, C.[Date]) <= case when J.[Phase] = 'Done' then convert(date,J.[History_Start_Time]) else convert(date,isnull(J.[History_End_Time],getdate())) end

[Digvijay_Singh]

Not sure but below script might help you to kickstart - 

Input:
Load *
from TABLE_J;

left join(TABLE_J)
Load *
from TABLE_C;

noconcatenate

Final:
Load *
resident Input
Where C.[Date] >= [History_Start_Time]
and (
(C.[Date] <= [History_Start_Time] and [Phase] = 'Done')
OR C.[Date]) <= coalesce([History_End_Time], today(1))
)

Drop Table Input;

[bilalgunay]

Hi, 

Thanks for your input.

Is it possible that we should have left join(Input) instead of left join(TABLE_J)?

[Digvijay_Singh]

My bad, you are right, it should be left join(Input).