I recently started a new project in which I am using Qlikview to more aesthetically display some databased information. I am trying to create a history of inventory using historical invoice and workorder inputs. This is the first ime I have ever used QlikView and am really struggling with specifying the data in the where conditions. I am only trying to view three document types within the DB (IC,RI and OV) from the beginning of 2011 to current.
ILDCT, ILMCU, ILICU, ILDCTO, ILDOCO, ILLITM, ILDGL, ILLNID, ILTRDJ, ILUNCS, ILLOCN, ILTRQT, ILTREX, ILPAID FROM "JDE_PRODUCTION".PRODDTA.F4111 Where ILDCT = 'IC' and ILTRDJ > 110365 or ILDCT = 'RI' and ILTRDJ > 110365 or ILDCT = 'OV' and ILTRDJ > 110365;
The Problem I'm having is that even if i 'debug' 150,000 lines from the DB im still only getting 1 month of data (Jan 2011). I feel like this might be due to the poorly constructed 'where' statement. If i think about it logically, I believe it has to read through the DB three times to meet my criteria. I haven't been able to discover a method for filtering the data other than in the 'where' statement. Any feedback or help would be greatly appriciated, as I am very new to QlikView and struggling! Thanks.
I reformatted your where clause for better understanding:
(ILDCT = 'IC' and ILTRDJ > 110365) or
(ILDCT = 'RI' and ILTRDJ > 110365) or
(ILDCT = 'OV' and ILTRDJ > 110365)
It should work as expected: Fetch all records with OrderDate > 31.12.2010 and having one of the three ILDCT-Values.
I do not think that this is a poorly SQL-Where Clause. The database will parse it and generate a query plan (means to translate it into DB-internal code and modules) and execute it once(!). An equivalent expression would be:
(ILDCT = 'IC' or ILDCT = 'RI' or ILDCT = 'OV') and ILTRDJ > 110365
But as mentioned above, both should work properly.
Are you sure that you got records with "ILTRDJ > 110365" in your table ?!? I would check this with a simple "Where ILTRDJ > 110365".