Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Nikhil2725
Creator II
Creator II

SQL queries

Hello,

I'm migrating an application to QlikSense. So far it has been a smooth process except migrating of a part of the application.

 Currently we use sql queries to generate the specified column.

 The query used looks like the one below.

SELECT Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2) AS Expr1

FROM (SELECT Sum(Invoice.GrossValue_LC) AS SumOfGrossValue_LC, Sum(Invoice.COGS_LC) AS SumOfCOGS_LC,

PH9.PH3, Invoice.BillingYear

FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr)

INNER JOIN PH9 ON Material.PH9 = PH9.PH9

GROUP BY PH9.PH3, Invoice.BillingYear

HAVING (((PH9.PH3)='" & PH6 & "' ) AND ((Invoice.BillingYear)='" & BillingYear & "')))  AS ord1

GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

The bold element in the "Having" clause in the SQL query will be dynamically selected by the User in the UI.

For example:Billing year=2011 and PH6=231546.

The above query results a percentage.

I do not want to use a SQL or LOAD statement as above. I instead want to use some QlikSense functions (Like Aggr()) and set analysis which will help in achieving the same result as the above query does.

 

Any suggestions please with an example?

3 Replies
jochem_zw
Partner Ambassador
Partner Ambassador

don't use the SQL queries, instead create a datamodel in Qlik. The power of Qlik is in the datamodel and it's associative engine. So just load Material as a table, invoices as a table, create a calendar table, and create a key for:

Calendar <=> Invoice

Material <=> Invoice

and do the sum and selections on year and material in qlik

Nikhil2725
Creator II
Creator II
Author

can u brief little more on how to create calendar table?

jochem_zw
Partner Ambassador
Partner Ambassador

 

Try to create something like this:

Community.png

 

 

 

 

 

 

 

 

with this model/script you can create your visualizations. you can filter on year, material etc. and have functions like sum(qty) an sum(amount) by year/month/date/material etc.

 

GL