Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 ;
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.
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.
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
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.
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;
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;
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.
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