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 query into Expression

Hi All,

I want to convert the below SQL statement into Expression.

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr)
INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

 

I'm using a filter for the Billing Year.

What ever the user selects year from the UI that value should come in "invoice.BillingYear" like wise for PH6 also

How can I achieve it??

16 Replies
Nikhil2725
Creator II
Creator II
Author

Any suggestions for the above query????

Im stuck in that:(

Nikhil2725
Creator II
Creator II
Author

Any Ideas??

 

Nikhil2725
Creator II
Creator II
Author

It Means I have to use dynamic selection on Having Clause...

HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "')))

Here in the above... user will select the PH6 Number and Billing year dynamically through the filters in the UI.

sunny_talwar

Would you be able to provide a sample where we can check this out?

Nikhil2725
Creator II
Creator II
Author

Hi Sunny,

Please find the below table...

Customer #Price GroupCustomer nameLocationRegionDivisionPH #PH nameQuantityGM VGGrossValue_LCCOGS 
512547Y1test customer 439City 43956KKS231546BV 546245%(This is desired output I needs to get)49722262.15

 

In the above table I need to calculate GM VG column.

For that in the backend we have use the following query.

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr) INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

Here in the above query in the HAVING condition &PH& and &year& will be selected by the Users in the UI..

In the above example table User has selected 231546 PH and 2010 billing year..

 

 

 

 

 

sunny_talwar

I believe Qlik Sense by default will show based on your selections... if you have selected those two... you should automatically see those rows where the condition is met.

Nikhil2725
Creator II
Creator II
Author

Hi Sunny,

If i execute that Query then only I will get that desired output....

 

sunny_talwar

Which query?

Nikhil2725
Creator II
Creator II
Author

Hi Sunny,

The below query I need to run 

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr) INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);