topic Translate sql script into qlikview in QlikView

Translate sql script into qlikview

Mon, 02 Apr 2012 07:37:51 GMT

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)

Translate sql script into qlikview

flipside — Mon, 02 Apr 2012 08:38:15 GMT

Hi,

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

flipside

Translate sql script into qlikview

Mon, 02 Apr 2012 09:25:47 GMT

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

Translate sql script into qlikview

flipside — Mon, 02 Apr 2012 09:52:22 GMT

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

Translate sql script into qlikview

Mon, 02 Apr 2012 13:36:45 GMT

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;

Translate sql script into qlikview

Clever_Anjos — Mon, 02 Apr 2012 13:42:57 GMT

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

Translate sql script into qlikview

Mon, 02 Apr 2012 13:46:25 GMT

Hey, 10x!

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

Have a nice day!

Translate sql script into qlikview

Clever_Anjos — Mon, 02 Apr 2012 13:48:28 GMT

"Position" field, isn´t it?

Translate sql script into qlikview

Mon, 02 Apr 2012 13:49:37 GMT

Yes!

Translate sql script into qlikview

Mon, 02 Apr 2012 14:05:21 GMT

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

Re: Translate sql script into qlikview

Clever_Anjos — Mon, 02 Apr 2012 14:12:17 GMT

Just concatenate 2 LOAD´s

YourTable:
LOAD Expr1;

(

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

FROM dbo.VAfurnizori AS F

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

);

CONCATENATE

LOAD Expr1;

(

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

Translate sql script into qlikview

Mon, 02 Apr 2012 16:29:23 GMT

And for difference between 2 or more select statement, i'll use a join?

Re: Translate sql script into qlikview

Clever_Anjos — Mon, 02 Apr 2012 17:13:44 GMT

Hi Olívia, Im not understanding you. Could you post a sample?

Translate sql script into qlikview

Mon, 02 Apr 2012 17:20:09 GMT

I also have a calculated field like next, where I have sums and differences between select statements:

ISNULL(SUM(AmountLocalCurrency), 0)

FROM dbo.VAclienti AS C +

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

10x again, Olivia

Translate sql script into qlikview

Clever_Anjos — Mon, 02 Apr 2012 19:23:02 GMT

Well, I´m not seeing the whole forest

I would do each load separated and joining or concatenat the results

Translate sql script into qlikview

Thu, 05 Apr 2012 08:52:02 GMT

Hi Olivia

it must be very difficult to translate the entire sql script into qv script in one big chunk, even for the most experienced developers. i would suggest breaking the process into smaller chunks, using a more systematic approach - create and control your own structure

1. identify all the tables used in the sql script

1.a. create qvds from these tables. for example:

VAclienti:

Load *

;

Select *

from dbo.VAclienti

;

Store VAclienti into VaClienti.qvd

;

Drop table VAclienti

;

2. explore the qvds, load each table separately and see what kind of data it contains. try to get a quick answer for the following points

- what kind of table is it? transactions, entities, coresp between bigger tables?

- identify key fields, unique IDs. use the sql script to make it more easy. a structure schema could prove really helpful

- which are dimensions and value fields (try to keep only the relevant ones - especially those found in the sql script, in your case)

- what is the level of detail? also known as aggregation level or granularity

3. create processed tables using the aggregation from the sql script (sum(amount) with group by and where statement). store these tables into new qvds. however! qlikview can manage data even if it's not pre-aggregated; so think twice before, this step might be irrelevant for the moment

4. use the sql script to figure out what is the most appropiate relationship method between the tables you have (connected using key fields? left/right/inner/outer join/keep? concatenate?) - however, i would suggest leaving them as separate tables for the begining and just keep in mind the relarionship method, this will help you figure out big errors

5. the last and maybe the most important - use the qlik view interface in order to constantly check the integrity of your structure. always have a draft qvw open for testing your data and your suppositions, if done properly, this draft often becomes the incipient version of your application

- do the key fields connect correctly? (data should have same format, key fields should have a big subset ratio - for example each client should have a name attributed from the clients nomenclature table)

- always check that your tables don't grow after joining one with another (undesired one to many association or cartesian product)

- create simple charts (bar charts, straight tables) using one dimension and one or two simple expressions, see if the values make sense, using your business knowledge. it's a good moment for starting some basic validation - compare the result with values extracted from erp reports, company stats and so on. make selections and see how they affect the result, the selections can be afterwards translated into where clauses into your script.

Translate sql script into qlikview

Thu, 05 Apr 2012 09:37:16 GMT

10x Cosmin! I take it step by step and I'll tell you what I succeded

Nice day!