SQL queries

[Nikhil2725]

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?

[jochem_zw]

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]

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

[jochem_zw]

Try to create something like this:

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