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)

[Clever_Anjos]

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

[#3]

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

[Clever_Anjos]

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

[#5]

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

[Clever_Anjos]

Well, I´m not seeing the whole forest

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

[#7]

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.

[#8]

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

Nice day!