topic Re: Adding multiple fields to find a desired value in QlikView

Adding multiple fields to find a desired value

Anonymous — Thu, 02 Aug 2012 00:06:50 GMT

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

Thu, 02 Aug 2012 09:11:55 GMT

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

Anonymous — Thu, 02 Aug 2012 18:28:23 GMT

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!

Re: Adding multiple fields to find a desired value

Fri, 03 Aug 2012 07:26:34 GMT

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

Re: Adding multiple fields to find a desired value

Anonymous — Mon, 06 Aug 2012 18:55:55 GMT

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.

Re: Adding multiple fields to find a desired value

MayilVahanan — Mon, 06 Aug 2012 19:10:29 GMT

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

Re: Adding multiple fields to find a desired value

Anonymous — Thu, 16 Aug 2012 17:36:24 GMT

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;