Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
cliff_clayman
Contributor II

How to create Qlik expressions from Access SQL

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];

5 Replies
Not applicable

Re: How to create Qlik expressions from Access SQL

Hey dude! Firstly, have u accessed the server to make sure the data is right? Secondly,  you can load the the data into Qlik the related field without "having", and then make some filters in the straight table, for example add expressions in order to get the same result. However the file will become a little bit larger. Hope it helps!

cliff_clayman
Contributor II

Re: How to create Qlik expressions from Access SQL

Yes, the data is correct.  The data isn't loaded with this query.  This is a separate query on the main table that I am trying to recreate in Qlik to create a straight table.  I am trying to add expressions in order to get the same result.  The issue is that I do not know how to recreate that HAVING code into expressions within Qlik.

Not applicable

Re: How to create Qlik expressions from Access SQL

Can you do the "having" clause in the loading script? FYR: having clause in Qlikview Hope it helps you cliff!

cliff_clayman
Contributor II

Re: How to create Qlik expressions from Access SQL

I don't have a lot of experience in scripting.  I was really hoping there was a way to handle this in an expression.

Not applicable

Re: How to create Qlik expressions from Access SQL

Maybe you can try to change the expression to use WHERE instead of Having?