topic How to create Qlik expressions from Access SQL in QlikView

How to create Qlik expressions from Access SQL

cliff_clayman — Tue, 28 Jun 2016 21:51:41 GMT

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

Re: How to create Qlik expressions from Access SQL

Wed, 29 Jun 2016 01:31:33 GMT

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!

Re: How to create Qlik expressions from Access SQL

cliff_clayman — Wed, 29 Jun 2016 14:01:06 GMT

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.

Re: How to create Qlik expressions from Access SQL

Thu, 30 Jun 2016 01:11:39 GMT

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

Re: How to create Qlik expressions from Access SQL

cliff_clayman — Thu, 30 Jun 2016 17:50:04 GMT

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

Re: How to create Qlik expressions from Access SQL

Thu, 30 Jun 2016 18:33:46 GMT

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