SQL to Qlikview Friendly

[#1]

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

[pat_agen]

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 ;

[#3]

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.

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

[pat_agen · Accepted solution]

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.

[#5]

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

[pat_agen]

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.

[#7]

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;

[#8]

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;

[pat_agen]

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.

[#10]

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