Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

6 Replies
Not applicable
Author

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] ;

Anonymous
Not applicable
Author

I tried running it as shown but I get an  "Aggregation expressions required by GROUP BY clause" error when I run it. I think there is some error in my idea of the NumSum command or something. Any idea on what i could do to make it work. Again, all I'm trying to do is add upp all three categories (Invoice, work order and transfer).

Thanks!

Not applicable
Author

Hi,

Why do you not try the following:

Inventory:

Load
    
[Item Number],
    
[Document Creation Date] as Date,   

     (Invoice+ [Work Order]+Transfer) as Amount

Resident DocInfo

Group By        

          [Item Number],

          [Document Creation Date] ;

Regards

Rahul

Anonymous
Not applicable
Author

I tried this as well, but I still got  an "Aggregation expressions required by GROUP BY clause" error when I run it. I believe I need to include a SUM function or some other aggregate expression. Howerver, if I try to sum muiltiple fields say like this:

Sum(Invoice, [Work Order],Transfer) as Amount

I get an error as well.

MayilVahanan

HI

     Try like this,

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

Resident DocInfo

Group By       

          [Item Number],

          [Document Creation Date] ;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Anonymous
Not applicable
Author

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;