Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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] ;
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!
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
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.
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 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;