Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bzim8481
New Contributor

Database 'where condition' problem

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.

1 Reply
Not applicable

Re: Database 'where condition' problem

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

Community Browser