16 Replies Latest reply: Apr 5, 2012 5:37 AM by Olivia Briceag RSS

    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)
        • Translate sql script into qlikview
          Dave Riley

          Hi,

           

           

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

           

           

          flipside

            • 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

                • Translate sql script into qlikview
                  Dave Riley

                  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

                      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

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

                            • 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!

                                • Translate sql script into qlikview
                                  Clever Anjos

                                  "Position" field, isn´t it?

                                      • 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

                                         

                                          • Re: Translate sql script into qlikview
                                            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

                                              • Translate sql script into qlikview

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

                                                  • Re: Translate sql script into qlikview
                                                    Clever Anjos

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

                                                      • Translate sql script into qlikview

                                                        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

                                                            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

                                                              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.