topic Re: SQL Coalesce and having clause in qlikview in QlikView

SQL Coalesce and having clause in qlikview

Anonymous — Tue, 21 Aug 2018 10:28:39 GMT

Hi, I am New to Qlik and I got a requirement where I need to change the Coalesce and havng in qlikview. I tried few possible ways when not able to get the solution. I am providing the sql code below which i need to replicate same in the qlikview load statement

SELECT [ID], MAX(Domain) AS Domain, MAX([Batches]) AS [batched received], SUM(delivered) as [Delivery Count]

FROM "db".de."Dataset"

GROUP BY id HAVING COALESCE(SUM(opens), 0) = 0

Any help would be appreciated

Thanks in advance

Suresh

Re: SQL Coalesce and having clause in qlikview

vishsaggi — Tue, 21 Aug 2018 16:19:24 GMT

You can use Alt() function with preceding load. May be try like

Table1:

LOAD ID, DOMAIN, Batches, Delivered, Opens;

SQL Select ID, DOMAIN, Batches, Delivered, Opens

FROM db.de.dataset;

NoConcatenate

Final:

LOAD *

WHERE OpenSum = 0;

LOAD ID,

Max(Domain) AS Domain,

Max(Batches) AS BatchedReceived,

Sum(Delivered) AS DeliveryCount,

Alt(Sum(Opens),0) AS OpenSum

Resident Table1

Group By ID;

Drop Table Table1;

Re: SQL Coalesce and having clause in qlikview

Anonymous — Wed, 22 Aug 2018 15:17:22 GMT

Hi Vishwa,

It worked Thank you.