SQL Coalesce and having clause in qlikview

[Anonymous]

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

[vishsaggi · Accepted solution]

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;

[Anonymous]

Hi Vishwa,

It worked Thank you.