topic Re: Replicating a join that is done in MS-SQL (T-SQL) in App Development

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

bilalgunay — Wed, 13 Apr 2022 08:04:08 GMT

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

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

Digvijay_Singh — Wed, 13 Apr 2022 22:20:34 GMT

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;

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

bilalgunay — Thu, 14 Apr 2022 07:33:48 GMT

Hi,

Thanks for your input.

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

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

Digvijay_Singh — Thu, 14 Apr 2022 09:12:13 GMT

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