5 Replies Latest reply: Jun 30, 2016 2:33 PM by Robert Kazmierczak RSS

    How to create Qlik expressions from Access SQL

    Cliff Clayman

      I have an Access query that I need to convert to be able to use with a Qlik straight table.  I am having a problem with the 'HAVING' section of the code below:

       

      I am not getting back the expected results from the expressions I created.    Mainly, the issue seems to be with getting '0' values in the SUM for [3PU].  How can I re-create that HAVING statement using expressions?

       

      SELECT

      TableA.[Country],

      TableA.[Div],

      TableA.[BusinessUnit],

      TableA.[Country] & TableA.[Div] AS CDLookup,

      TableA.[VendorName],

      TableA.[S Rating],

      TableA.[S Rating Type],

      TableA.[RFC Month],

      Sum(TableA.[3PU]) AS [3PU Total]

       

      FROM

      TableA

       

      GROUP BY

      TableA.[Country],

      TableA.[Div],

      TableA.[BusinessUnit],

      TableA.[Country] & TableA.[Div],

      TableA.[VendorName],

      TableA.[S Rating],

      TableA.[S Rating Type],

      TableA.[RFC Month]

       

      HAVING

      (((Sum(TableA.[3PU]))<>0)

      AND ((TableA.[RFC Month]) Is Not Null)) OR (((Sum(TableA.[3PU]))<>0)

      AND ((TableA.[S Rating Type])<>"A"))

       

      ORDER BY

      TableA.[Country],

      TableA.[Div];