Translate sql script into qlikview

[#1]

Hello, can you help me pls to translate the next script into qlikview ? Tx!   (I undelined the fields which must be returned by the first select statement)  :

SELECT BranchOfficeName, BranchOfficeCode, Position, SAPPartnerName, SAPpartnerCode, BusinessArea, CostCenter, RevenueType, VG, Relation, LEFT(Position, 2) AS Month, LEFT(BookingMonth, 4) AS Year, ISNULL(SUM(AmountLocalCurrency), 0) AS Sales,

(SELECT ISNULL(SUM(Amount), 0) AS Expr1

FROM dbo.VAclientiAccruals AS SA

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position)) AS SalesAccruals,

(SELECT ISNULL(SUM(AmountLocalCurrency), 0) AS Expr1

FROM dbo.VAfurnizori AS F

WHERE (C.BranchOfficeName = BranchOfficeName) AND (C.Position = Position) AND (C.BookingMonth =        BookingMonth)) +

(SELECT ISNULL(SUM(CostAllocation), 0) AS Expr1

FROM dbo.VAinterface2 AS V2

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position) AND (C.BookingMonth = BookingMonth)) AS Cost,

(SELECT ISNULL(SUM(Amount), 0) AS Expr1

FROM dbo.VAfurnizoriAccruals AS CA

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position)) AS CostAccruals,

ISNULL(SUM(AmountLocalCurrency), 0) +

ISNULL ((SELECT ISNULL(SUM(Amount), 0) AS Expr1

FROM dbo.VAclientiAccruals AS SA

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position)), 0) -

(SELECT ISNULL(SUM(AmountLocalCurrency), 0) AS Expr1

FROM dbo.VAfurnizori AS F

WHERE (C.BranchOfficeName = BranchOfficeName) AND (C.Position = Position)) -

(SELECT ISNULL(SUM(CostAllocation), 0) AS Expr1

FROM dbo.VAinterface2 AS V2

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position)) -

ISNULL((SELECT SUM(Amount) AS Expr1

FROM dbo.VAfurnizoriAccruals AS CA

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position)), 0) AS GP

FROM dbo.VAclienti AS C

GROUP BY BranchOfficeName, BranchOfficeCode, Position, BookingMonth, SAPPartnerName, SAPpartnerCode, BusinessArea, CostCenter, RevenueType, VG, Relation, LEFT(Position, 2), LEFT(BookingMonth, 4)

[flipside]

Hi,

Can you not just put the code you already have into a view and call that instead?

flipside

[#3]

Yes,would be much easy so, but the result is not good. So I have to change the formula and I'm trying to find out what I have there first and what data from each table brings that script.

Olivia

[flipside]

Hi Olivia,

You could try just adding the preceding LOAD command in front of your SQL statement ...

LOAD <list of fields>;

SQL SELECT <your sql statement>;

... or if you are looking to break up the command you could rewrite using left joins such as this example ...

VAclienti:

LOAD

BranchOfficeCode,

BranchOfficeName,

Position,

BranchOfficeCode & Position as SalesAccrualKey;

SQL SELECT BranchOfficeCode, BranchOfficeName, Position FROM dbo.VAclienti

left join

LOAD

Branch & Position as SalesAccrualKey,

SalesAccruals;

SQL SELECT Branch, Position, ISNULL(SUM(Amount), 0) as SalesAccruals FROM dbo.VAclientiAccruals

group by Branch, Position;

If you leave out the left join, you will get two tables linked by the SalesAccrualKey, which is another option.

Hope this helps

flipside

[#5]

Hello again,

I am trying to make the first sum in first select statement and it gives me an error ("OLEDB read failed").

I don't see why...

VAclienti:
LOAD

BranchOfficeCode,
BranchOfficeName,
Position,
BranchOfficeCode & Position as SalesAccrualKey,
BusinessArea,
SAPpartnerCode,
VG,
Relation,
RevenueType,
Sales,
CostCenter,
Position,
BookingMonth,
SAPPartnerName,
left(Position,2) as Month,
left(BookingMonth,4) as Year;

sql select
BranchOfficeCode,
BranchOfficeName,
Position,
BusinessArea,
SAPpartnerCode,
VG,
Relation,
RevenueType,
CostCenter,
Position,
BookingMonth,
SAPPartnerName,
ISNULL(SUM(AmountLocalCurrency),0) as Sales

FROM dbo.VAclienti

group by BranchOfficeCode, BranchOfficeName, Position, BusinessArea, SAPpartnerCode, VG, Relation, RevenueType, CostCenter, BookingMonth, SAPPartnerName;

[Clever_Anjos]

"("OLEDB read failed " are hard to debug.
Try commenting fields from LOAD and check if they work

[#7]

Hey, 10x!

I've just realised where the problem was... I had 2 fields with the same name... ...

Have a nice day!

[Clever_Anjos]

"Position" field, isn´t it?

[#9]

Yes!

[#10]

Now I have other question: how do I transpose the next script, where I have a sum of two select statements:

(

SELECT ISNULL(SUM(AmountLocalCurrency), 0) AS Expr1

FROM dbo.VAfurnizori AS F

WHERE (C.BranchOfficeName = BranchOfficeName) AND (C.Position = Position)      AND (C.BookingMonth = BookingMonth)

)

+

(

SELECT ISNULL(SUM(CostAllocation), 0) AS Expr1

FROM dbo.VAinterface2 AS V2

WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position) AND (C.BookingMonth = BookingMonth)

)

AS Cost