1 Reply Latest reply: Jul 28, 2012 5:06 AM by Roland Kunle RSS

    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.

        • 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