6 Replies Latest reply: Aug 16, 2012 1:36 PM by bzim8481 RSS

    Adding multiple fields to find a desired value

      I recently took on an Inventory project in which I am calcualting inventory by adding work orders and transfers and minusing invoices. I have hit a wall when it comes to the actuall mathematical calculation, and as I am very new to QlikView, I am at a loss. My current script looks like this.

       

      ODBC CONNECT TO JDE_PROD

       

      ////////////////////////////////////////////////
      // F4111 Loading Item Ledger Info
      ////////////////////////////////////////////////
      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],
          
      ILUNCS/10000 as [Unit Cost],
          
      ILLOCN as [Location],
          
      ILTREX as [Explanation],
          
      ILPAID/100 as [Extended Cost/Price],
          
      Floor(ILTRQT/10000) as [Quantity],
          
      If(Len(text(ILCRDJ))>5,Date(MakeDate(1900 + (left(text(ILCRDJ),1) * 100) + mid(text(ILCRDJ),2,2)) + (right(text(ILCRDJ),3) - 1)),if(ILCRDJ > 0 ,Date(MakeDate(1000 + (left(text(ILCRDJ),1) * 100) + mid(text(ILCRDJ),1,2)) + (right(+text(ILCRDJ),3) - 1)), Null())) as [Creation Date]
      ;SQL SELECT
           ILDCT, ILMCU, ILICU, ILDCTO, ILDOCO, ILLITM, ILDGL, ILLNID, ILUNCS, ILLOCN, ILTREX, ILPAID, ILCRDJ, ILTRQT
      FROM "JDE_PRODUCTION".PRODDTA.F4111
      Where (ILDCT = 'IC' and ILCRDJ > 109365)
      or (ILDCT = 'RI' and ILCRDJ > 109365)
      or (ILDCT = 'OV' and ILCRDJ > 109365);

      ////////////////////////////////////////////////
      //Date and Calendar Info
      ////////////////////////////////////////////////
      DateInfo:
      Load DISTINCT
          
      [Creation Date],
          
      Year([Creation Date]) as Year,
          
      Month([Creation Date]) as Month,
          
      WeekEnd([Creation Date]) as [Week End],
           'Q' &
      Ceil(Month(num([Creation Date]) + Right([Creation Date], 3) - 1)/3) as Quarter

      Resident ItemLedger;

      ////////////////////////////////////////////////
      //Document Specs
      ////////////////////////////////////////////////
      DocInfo:
      Load
          
      [Item Number],
          
      [Business Unit] as Branch,
          
      [Creation Date] as [Document Creation Date],
          
      If(text([Document Type]) = 'RI', [Quantity]) as Invoice,
          
      if(text([Document Type]) = 'IC', [Quantity]) as [Work Order],
          
      if(text([Document Type]) = 'OV', [Quantity]) as Transfer

      Resident ItemLedger;

      ////////////////////////////////////////////////
      //Inventory Info
      ////////////////////////////////////////////////
      Inventory:
      Load
          
      [Item Number],
          
      [Document Creation Date] as Date,
          
      NumSum(Invoice, [Work Order], Transfer) as Amount

      Resident DocInfo;

      Once I reach this point, however, I cant figure out how to complete the formula! I have also tried several ways to sum the data in the chart wizard expressions formulas but thuis far have been unsuccessful. If anyone could give some pointer on how to do this simple task it would be greatly appriciated!

       

      Thanks

        • Re: Adding multiple fields to find a desired value
          Rahul Lakhina

          I believe you need a group by clause:

           

          Like:

           

          Inventory:

          Load
              
          [Item Number],
              
          [Document Creation Date] as Date,
              
          NumSum(Invoice, [Work Order], Transfer) as Amount

          Resident DocInfo

          Group By        

                    [Item Number],

                    [Document Creation Date] ;

          • Re: Adding multiple fields to find a desired value

            Thanks you everyone for the responses! I had to make some changes but I was able to figure out the calcualtion and now have a daily inventory fluctuation for each day (Thats what was put in minus what was taken out). Now however, I face another problem.

             

            In order to get a history of inventory I need to take the curent inventory, which is available, and subtract or add the inventory fluctuation backwards from the curent inventory. This has to be strung out backwards from the current one based on the changes everyday. Any ideas on how to do this?

             

            My script looks like this:

             

            ODBC CONNECT TO JDE_PROD (XUserId is aaKRPYJOPDZIGaFMLH, XPassword is bBJRCYJOPDZIGaFMVH);
            ////////////////////////////////////////////////
            // F4111 Loading Item Ledger Info
            ////////////////////////////////////////////////
            ItemLedger:
            LOAD
                  
            ILDCT as [Document Type],
                  
            ILMCU as [Business Unit],
                  
            ILDCTO as [Order Type],
                  
            ILDOCO as [Order Number],
                  
            If(IsNum(ILLITM), ILLITM) As [Item Number],
                  
            ILITM as [Item Number (Short)],   ILLNID/1000 as [Line Number],
                  
            ILUNCS/10000 as [Cost],
                  
            ILLOCN as [Location Key],
                  
            ILTREX as [Explanation],
                  
            Floor(ILTRQT/10000) as [Quantity],
                  
            If(ILDCT = 'RI', ILTRQT/10000) as [Invoice Key],
                  
            If(ILDCT = 'IC', ILTRQT/10000) as [Work Order Key],
                  
            If(ILDCT= 'OV', ILTRQT/10000) as [Transfer Key],
                  
            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]
            ;SQL SELECT
                   ILDCT, ILMCU, ILDCTO, ILDOCO, ILLITM, ILLNID, ILUNCS, ILLOCN, ILTREX, ILTRDJ, ILTRQT, ILITM
            FROM "JDE_PRODUCTION".PRODDTA.F4111
            Where (ILDCT = 'IC' and ILTRDJ > 109365 and Len(ILLITM) = 6)
            or (ILDCT = 'RI' and ILTRDJ > 109365 and Len(ILLITM) = 6)
            or (ILDCT = 'OV' and ILTRDJ > 109365 and Len(ILLITM) = 6);

            ////////////////////////////////////////////////
            //Date and Calendar Info
            ////////////////////////////////////////////////
            DateInfo:
            Load DISTINCT
                  
            [Order Date],
                  
            Year([Order Date]) as Year,
                  
            Month([Order Date]) as Month,
                  
            WeekEnd([Order Date]) as [Week End],
                   'Q' &
            Ceil(Month(num([Order Date]) + Right([Order Date], 3) - 1)/3) as QuarterResident ItemLedger;

            CurrentDateTemp:Load
                  
            Date(Max([Order Date]), 'M/D/YYYY') as CurrentDateResident ItemLedger;
            Let vMaxVar = Peek('CurrentDate');


            ////////////////////////////////////////////////
            //Step 1 Inventory Calc
            ////////////////////////////////////////////////
            InventoryTemp:
            Load Distinct
                  
            [Item Number],
                  
            Sum([Invoice Key]) as Invoice,
                  
            Sum([Work Order Key]) as [Work Order],
                  
            Sum([Transfer Key]) as TransfersResident ItemLedger
                  
            Group By
                         
            [Item Number];
            //////////////////////////////////////////////////
            //// Inventory Info
            //////////////////////////////////////////////////
            InventoryInfo:
            Load Distinct
                  
            [Item Number],
                  
            Sum(Invoice+[Work Order]+Transfers) as [Inventory Change]Resident InventoryTemp
                  
            Group By
                  
            [Item Number];
            DROP Table InventoryTemp;
            ////////////////////////////////////////////////
            //Current Inventory Info
            ////////////////////////////////////////////////
            ItemLocationFile:
            LOAD
                  
            LIITM as [Item Number (Short)],
                  
            LIMCU as [Business Unit],
                  
            LILOCN as Location,
                  
            LIGLPT as [G/L Category],
                  
            LIPQOH/10000 As [Qty On Hand]
            ;SQL SELECT
                   LIITM, LILOCN, LIGLPT, LIPQOH, LIMCU
            FROM "JDE_PRODUCTION".PRODDTA.F41021
            where (LIPQOH <> 0);

            ////////////////////////////////////////////////
            //Current Inventory Final
            ////////////////////////////////////////////////
            CurrentInv:
            Load Distinct
                  
            [Item Number (Short)],
                  
            Sum([Qty On Hand]) as [Total Current Inventory]Resident ItemLocationFile
                  
            Group By
                         
            [Item Number (Short)];
                         
            Drop Table ItemLocationFile;