Database 'where condition' problem

Anonymous — Fri, 27 Jul 2012 23:34:34 GMT

Hello,

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.

This is what i have so far:

ODBC

CONNECT to JDE

ItemLedger:

LOAD

ILDCT as [Document Type],
ILMCU as [Business Unit],
ILICU as [Batch Number],
ILDCTO as [Order Type],
ILDOCO as [Order Number],
If(IsNum(ILLITM), ILLITM) As [Item Number],
If(Len(text(ILDGL))>5,Date(MakeDate(1900 + (left(text(ILDGL),1) * 100) + mid(text(ILDGL),2,2)) + (right(text(ILDGL),3) - 1)),if(ILDGL > 0 ,Date(MakeDate(1000 + (left(text(ILDGL),1) * 100) + mid(text(ILDGL),1,2)) + (right(+text(ILDGL),3) - 1)), Null())) as [G/L Date],
ILLNID/1000 as [Line Number],
If(Len(text(ILTRDJ))>5,Date(MakeDate(1900 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),2,2)) + (right(text(ILTRDJ),3) - 1)),if(ILTRDJ > 0 ,Date(MakeDate(1000 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),1,2)) + (right(+text(ILTRDJ),3) - 1)), Null())) as [Order Date],
ILUNCS/10000 as [Unit Cost],
ILLOCN as [Location],
Floor(ILTRQT/10000) as [Transaction Quantity],
ILTREX as [Explanation],
ILPAID/100 as [Extended Cost/Price]
;
SQL

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.

Re: Database 'where condition' problem

Sat, 28 Jul 2012 09:06:56 GMT

Hi,

I reformatted your where clause for better understanding:

Where

(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.

Notes:

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:

Where

(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".

GooD LucK

Roland

topic Re: Database 'where condition' problem in QlikView

Database 'where condition' problem

Re: Database 'where condition' problem