9 Replies Latest reply: May 18, 2011 9:18 AM by Ian Kingon RSS

    SQL to Qlikview Friendly

      One of the company developers sent through the following code:

       

      SELECT      JOB.JOB_DATE,

                  JOB.JOB_NO,

                  PACKDET.PO_NO,

                  VOLUME = SUM(PACKDET.NETQUANTITY * PRODUCT.VOLUME),

                  PCS = SUM(PACKDET.NETQUANTITY),

                  STATUS = CASE WHEN JOB.CONFIRMED = 'Y' THEN 'CONFIRMED'

                              WHEN JOB.ALLOCATED = 'Y' THEN 'IN PROGRESS'

                              ELSE 'NOT YET ALLOCATED' END,

                  JOB.PRIN_CODE

      FROM JOB, PACKDET, PRODUCT 

      WHERE JOB.JOB_TYPE ='IMP'

      AND   CONVERT(DATE,JOB.JOB_DATE) >='01/01/2011'

      AND   JOB.PRIN_CODE = PACKDET.PRIN_CODE

      AND   JOB.JOB_NO  = PACKDET.JOB_NO

      AND   PACKDET.PRIN_CODE = PRODUCT.PRIN_CODE

      AND   PACKDET.PROD_CODE = PRODUCT.PROD_CODE

      GROUP BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO,JOB.PRIN_CODE,

            CASE WHEN JOB.CONFIRMED = 'Y' THEN 'CONFIRMED'

            WHEN JOB.ALLOCATED = 'Y' THEN 'IN PROGRESS'

            ELSE 'NOT YET ALLOCATED' END

      ORDER BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO

       

      How do I best convert this to be Qlikview friendly?

       

      Alternatively, I could import a table for the Job_Details: by JOB_NO and JOB_DATE and use a WHERE clause to limit the dates to >=01/01/2011.  I then create a second table called Packing_Details: which would effectively hold multiple line items per job, but how do I limit these so that I only import those JOB_NOs that have already been imported into Job_Details:?

       

      I would really appreciate the Community help on this?

       

      Thanks

      Ian

        • Re: SQL to Qlikview Friendly
          Patrick Laredo

          hi Ian,

           

          I was intrigued by your post. What do you mean make this qlikview friendly? Apart from some syntaxe I am unfamiliar with (the use of the "=" sign in the list of fields) this looks like fairly standard sql.

           

          If you used this in a qv document you woud pick up a table with seven fields (JOB_DATE thru PRIN_CODE) and then you would do whateaver you wished to in qv.

           

          If you have access to the DBMS and you wanted to break up the sql join you coudl write soem code as follows (be aware this is not tested):

           

          LoadJobs:
          load
           JOB_DATE as Jobdate,
           JOB_NO as JobNo,
           PRIN_CODE as PrinCode,
           if(CONFIRMED = 'Y','CONFIRMED',
            if(ALLOCATED = 'Y','IN PROGRESS',
             'NOT YET ALLOCATED'))  as  Jobstatus,
           JOB_NO & '|' & PRIN_CODE  as packDetKey
           ;
           
          sql select 
           JOB.JOB_DATE,
                  JOB.JOB_NO,
                  JOB.PRIN_CODE,
           JOB.CONFIRMED,
           JOB.ALLOCATED
          from JOB
          WHERE JOB.JOB_TYPE ='IMP' 
          AND   CONVERT(DATE,JOB.JOB_DATE) >='01/01/2011';
          LoadPackDet:
          load 
           JOB_NO & '|' & PRIN_COD  as packDetKey,
           PROD_CODE     as prodKey,
           PO_NO          as PONo,
           NETQUANTITY    as NetQuantity
          where exists(packDetKey,JOB_NO & '|' & PRIN_COD)
          ;
          sql select 
           JOB_NO,
                  PRIN_CODE,
           PO_NO,
           NETQUANTITY
          from PACKDET;
          LoadProduct:
          load 
           PROD_CODE   as prodKey,
           VOLUME    as volume
          where exists(prodKey,PROD_CODE)
          ;
          sql select 
           PROD_CODE,
           VOLUME
          from PRODUCT ;
          
          
            • SQL to Qlikview Friendly

              Hi Pat,

               

              I will try the combined code next, but what you have suggested is exactly what I am trying to do.  I have loaded part of my script below:

               

              Inbound_Job:
              LOAD
              // Load Table Keys
              JOB_DATE   As [Key Inbound_Job JOB_DATE],
              JOB_NO&'|'&PRIN_CODE  As PackDetKey,
              // Load Table Data
              JOB_DATE   As [Inbound_Job JOB_DATE],
              JOB_NO    As [Inbound_Job JOB_NO],
              PRIN_CODE    As [Inbound_Job PRIN_CODE],
              If(COMPLETED ='Y','Completed',If(CONFIRMED='Y','Confirmed',If(ALLOCATED ='Y','Allocated','In Progress'))) As [Inbound_Job JOB_STAT];
              SQL SELECT *
              FROM GACWAREDB.dbo.JOB WHERE JOB.JOB_TYPE='IMP'
              AND CONVERT(DATE,JOB.JOB_DATE)>='01/01/2011';

               

              Packing_Details:
              LOAD
              // Load Table Keys
              JOB_NO&'|'&PRIN_CODE  As PackDetKey,
              // Load Table Data
              JOB_NO   As [Inbound_Job JOB_NO],
              PO_NO   As [Inbound_Job PO_NO],
              PRIN_CODE  As [Inbound_Job PRIN_CODE],
              PROD_CODE  As [Inbound_Job PROD_CODE],
              NETQUANTITY  As [Inbound_Job NETQUANTITY];
              SQL SELECT *
              FROM GACWAREDB.dbo.PACKDET WHERE EXISTS (PackDetKey,JOB_NO&'|'&PRIN_CODE);

               

              Unfortunately, I am getting a Syntax error from the Script Editor when it hits the PackDetKey in the WHERE EXISTS select statement.  I have attached a screenshot of the error.

              QV Script Error.bmp

              Much appreciated . . . I will try the join next.

                • SQL to Qlikview Friendly
                  Patrick Laredo

                  hi Ian,

                   

                  look carefully at the code I sent. The exists() command was used in the Load statements. This is qlikview syntax (albeit untested by myself). You have put the exits command in your sql statement. And this is being kicked back. I don't know details of sql syntax for sybase I just know that going through ODBC links you are best off sticking to basic sql. Let qlikview handle the interesting/sohisticated stuff.

                   

                  Try it as I coded it and see if it works.

                    • SQL to Qlikview Friendly

                      Hi Pat,

                       

                      Okay, thanks for that - worked a treat . . . seem to have generated a synthetic key that was unintended, but should be able to sort that out.  I have not used SQL since I left university in 1989 . . . but as I am trying to reinvent myself I am having to roll up the sleeves.

                       

                      For the benefit of a complete novice, but one who is eager to learn, can you briefly explain the difference between LOAD and SELECT?  Does one have to precede the other (LOAD and then SELECT I would presume)?  Is there an implicit LOAD in your code?  Where does the QV code start and stop and what is sent through to the SQL driver to communicate withthe DB?

                       

                      Should I be able to run the initial code posted from within QV?  In other words, I have only ever loaded single tables and used a key field to link them.  Being able to build a single table from 3 database tables upfront could simplify my data model considerably.

                       

                      Yikes, re-reading this, I realise how little I know and what a beginner I sound like, but I guess that is what happens when you start from scratch.

                       

                      Thanks

                      Ian

                        • SQL to Qlikview Friendly
                          Patrick Laredo

                          Hi Ian,

                           

                          For starters an extremely useful ressource is the "help" button inside qlikview.

                           

                          In this example the sql part(s) is/are all the commands beginning with "sql" in my code. Basically this is just requesting the database to send back , via the odbc link, the fields requested. Outside of these commands everything is Qlikview.

                           

                          The load statement that precedes an "sql select" tells qlikview what to do with the dataset the sql will send back to it. This, I think, is one of the really good points in qv. You learn the qv syntax and with just basic sql knowledge you can start to do some really interesting stuff.

                           

                          Your last question

                          "Should I be able to run the initial code posted from within QV? " was why I replied to your mail in the first place as your post was entitled "how to make this friendly". In fact there is nothing unfriendly about it. If that is a bona fide sql query that the odbc driver for your database can handle then just put it straight into qlikview preceeding it by the "sql" keyword. If you want to do further data processing (renaming fileds, doing some "if .. else" transformations etc. etc.)  on the result set then use a preceding load statement.

                           

                          QV offers huge possibilities to transform your original data set because you are basically passing your data through a programme during the refresh cycle, your sql query (or load from xl or whatever datasource you are going against) is just the starting point - you are not just limited to preceeding load staements by the way - and even after that the UI offers even further possibilities through set analysis and so on to go an extra mile. With pure sql the answer has to be in the query which means things can get extremely complicated as the data is pulled this way and that.

                           

                          As I see you only got out of uni a little time ago you've got plenty of time to learn

                           

                          happy qliking.

                            • SQL to Qlikview Friendly

                              Hi Pat,

                               

                              I have been using the help and the detailed 1400+ page manual . . . sometimes it just needs the steer of a person though to get you on the straight and narrow.  I also understand the LOAD and SELECT statements much better.  Thanks a million.

                               

                              My next step is to try and get the SQL code as supplied to work - as this will as you pointed out give me a table with 7 columns and I do not even have to bring some of the data into QV.  I can see that there could be a case for either depending on your need.  Right now it would be beneficial to be able to do the joins within the SQL and only create the 7 table column.  I have re-written the inital SQL to include both explicit LOAD statements and then the SQL almost verbatim as I initially had it at the top of the post.

                               

                              I do however get a syntax error as shown as soon as it hits the FROM statement?  Any steer?

                               

                              SQL##f - SqlState: 42000, ErrorCode: 156, ErrorMsg: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Incorrect syntax near the keyword 'FROM'.

                               

                              QV Script

                              Inbound_Job:

                              LOAD

                              // Load Table Keys

                              JOB.JOB_DATE     As [Key Inbound_Job JOB_DATE],

                              JOB.JOB_NO      As [Key Inbound_Job JOB_NO],

                              // Load Table Data

                              JOB.JOB_DATE     As [Inbound_Job JOB_DATE],

                              JOB.JOB_NO      As [Inbound_Job JOB_NO],

                              JOB.PRIN_CODE      As [Inbound_Job PRIN_CODE],

                              PACKDET.PO_NO     As [Inbound_Job PO_NO],

                              STATUS       As [Inbound_Job JOB_STAT],

                              VOLUME       As [Inbound_Job PO_NO],

                              PIECES       As [Inbound_Job PO_NO];

                              SQL

                              SELECT JOB.JOB_DATE,

                                JOB.JOB_NO,

                                JOB.PRIN_CODE,

                                PACKDET.PO_NO,

                                VOLUME = SUM(PACKDET.NETQUANTITY*PRODUCT.VOLUME),

                                PIECES =SUM(PACKDET.NETQUANTITY),

                                STATUS = CASE WHEN JOB.COMPLETED ='Y' THEN 'Completed'

                                    WHEN JOB.CONFIRMED='Y' THEN 'Confirmed'

                                    WHEN JOB.ALLOCATED ='Y' THEN 'Allocated'

                                    ELSE 'In Progress' END,

                              FROM GACWAREDB.dbo.JOB, GACWAREDB.dbo.PACKDET, GACWAREDB.dbo.PRODUCT

                              WHERE JOB.JOB_TYPE='IMP'

                              AND CONVERT(DATE,JOB.JOB_DATE)>='01/01/2011'

                              AND JOB.PRIN_CODE=PACKDET.PRIN_CODE

                              AND JOB.JOB_NO=PACKDET.JOB_NO

                              AND PACKDET.PRIN_CODE=PRODUCT.PRIN_CODE

                              AND PACKDET.PROD_CODE=PRODUCT.PROD_CODE

                              GROUP BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO,JOB.PRIN_CODE,

                              ORDER BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO;

                                • SQL to Qlikview Friendly

                                  Arrggh!  Sorry Pat,

                                   

                                  Stupid and simple syntax that I have now sorted so ignore the post above - still working on it, but I am now getting a JOB.JOB_DATE field not found error?

                                   

                                  If you can see something I am doing wrong let me know - it will be much appreciated.

                                   

                                  Regards

                                  Ian

                                   

                                  Current script posted below for clarity:

                                   

                                  Inbound_Job:

                                  LOAD

                                  // Load Table Keys

                                  JOB.JOB_DATE     As [Key Inbound_Job JOB_DATE],

                                  JOB.JOB_NO      As [Key Inbound_Job JOB_NO],

                                  // Load Table Data

                                  JOB.JOB_DATE     As [Inbound_Job JOB_DATE],

                                  JOB.JOB_NO      As [Inbound_Job JOB_NO],

                                  JOB.PRIN_CODE      As [Inbound_Job PRIN_CODE],

                                  PACKDET.PO_NO     As [Inbound_Job PO_NO],

                                  STATUS       As [Inbound_Job JOB_STAT],

                                  VOLUME       As [Inbound_Job PO_NO],

                                  PIECES       As [Inbound_Job PO_NO];

                                  SQL

                                  SELECT JOB.JOB_DATE,

                                    JOB.JOB_NO,

                                    JOB.PRIN_CODE,

                                    PACKDET.PO_NO,

                                    VOLUME = SUM(PACKDET.NETQUANTITY*PRODUCT.VOLUME),

                                    PIECES =SUM(PACKDET.NETQUANTITY),

                                    STATUS = CASE WHEN JOB.COMPLETED ='Y' THEN 'Completed'

                                        WHEN JOB.CONFIRMED='Y' THEN 'Confirmed'

                                        WHEN JOB.ALLOCATED ='Y' THEN 'Allocated'

                                        ELSE 'In Progress' END

                                  // FROM JOB, PACKDET, PRODUCT

                                  FROM GACWAREDB.dbo.JOB, GACWAREDB.dbo.PACKDET, GACWAREDB.dbo.PRODUCT

                                  WHERE JOB.JOB_TYPE='IMP'

                                  AND CONVERT(DATE,JOB.JOB_DATE)>='01/01/2011'

                                  AND JOB.PRIN_CODE=PACKDET.PRIN_CODE

                                  AND JOB.JOB_NO=PACKDET.JOB_NO

                                  AND PACKDET.PRIN_CODE=PRODUCT.PRIN_CODE

                                  AND PACKDET.PROD_CODE=PRODUCT.PROD_CODE

                                  GROUP BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO,JOB.PRIN_CODE

                                  ORDER BY JOB.JOB_DATE,JOB.JOB_NO,PACKDET.PO_NO;

                                    • SQL to Qlikview Friendly
                                      Patrick Laredo

                                      hi Ian,

                                       

                                      no I cannot see what is wrong in your sql statement. You need to check this against your original data source.

                                       

                                      As you are getting into this you should add another tool to your developement kitbag. This would be a database tool. It is much quicker to work out sql issues directly on the database the data comes from.

                                      I use a tool called Toad - look it up on google. You'll need to get the version that goes with whichever dbms - sybase it seems to be given one of your earlier posts -  you are using.

                                      This is very useful for debugging sql issues.

                                       

                                      These are not qlikview issues but as Steve Dark points out in his recent blog  http://www.quickintelligence.co.uk/what-makes-a-qlikview-developer/ a Qlikview developer needs to have several strings to his/her bow.

                                       

                                      keep persevering, you'll get there.

                                        • SQL to Qlikview Friendly

                                          Hi Pat,

                                           

                                          Thanks for the encouragement and the pointer to TOAD.  Will download later tonight.

                                           

                                          Managed to work out that QV was complaining about the JOB.JOB_DATE.  As soon as I dropped the preceding table name, viola, everything worked.  But the process of troubleshooting has increased my understanding and knowledge immensely.

                                           

                                          Thanks for the patience and support.

                                           

                                          Ian