Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Translate sql script into qlikview

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)
16 Replies
flipside
Valued Contributor II

Translate sql script into qlikview

Hi,

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

flipside

Not applicable

Translate sql script into qlikview

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
Valued Contributor II

Translate sql script into qlikview

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

Not applicable

Translate sql script into qlikview

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;

Employee
Employee

Translate sql script into qlikview

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

Not applicable

Translate sql script into qlikview

Hey, 10x!

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

Have a nice day!

Employee
Employee

Translate sql script into qlikview

"Position" field, isn´t it?

Not applicable

Translate sql script into qlikview

Yes!

Not applicable

Translate sql script into qlikview

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

Community Browser