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
sunny_talwar

In SQL or QlikView? I am not entirely sure I am able to understand the issue brother

Nikhil2725
Creator II
Creator II
Author

I want to convert that SQL query into Qliksense..

sunny_talwar

I guess you want to execute this query, but you want to do it in Qlik Sense. I am completely lost... may be someone else might be able to understand the issue and help you out here.

Best,
Sunny

Nikhil2725
Creator II
Creator II
Author

Can we convert this SQL query into expression in qliksense?

sunny_talwar

I am sure you can, but without seeing the output of this query and without really understanding what you are trying to get, it would be very very difficult to tell you what you need to do to convert it into an expression

Nikhil2725
Creator II
Creator II
Author

Sunny,

Have a look on below...

Please find the below table...

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

 

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.PH6)='" & PH6 & "' ) AND ((Invoice.BillingYear)='" & BillingYear & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

 

SumOfGrossValue_LC=4972

COGS =2262.15

Billing year=2011

PH6-231546.

 

HAVING (((PH9.PH6)='" & PH6 & "' ) AND ((Invoice.BillingYear)='" & BillingYear & "'))) AS ord1 In this statement it should change dynamically the PH6 value and BIlling year..

U got any Idea?

 

sunny_talwar

"U got any Idea?"

NOPE, Sorry