SQL query into Expression

[Nikhil2725]

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??

[sunny_talwar]

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

[Nikhil2725]

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]

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]

Sunny,

Have a look on below...

Please find the below table...

Customer #	Price Group	Customer name	Location	Region	Division	PH #	PH name	Quantity	Expr1	GrossValue_LC	COGS
512547	Y1	test customer 439	City 439	56	KKS	231546	BV 546	2	45%(This is desired output I needs to get)	4972	2262.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