Where's the best place to manipulate data?

[Anonymous]

I am working on a EOQ project for inventory and am pulling data from a JDE DB. I am very new to Qlikview andam confused/curious about where or how you can manipulate data with formulas. For my EOQ formula I need:

D, Ave Annual Demand

S, Setup Time

I, Carrying Cost

C, Unit cost

All of which should be linked via common Item number and Branch number. I can calculate Ave Annual demand based off of a sales table within my JDE DB, and I am using a formula to get from each individual sale to ave annual demand. Similarly, my setup time is determined by averging setup times from another JDE table. Unit cost can be pulled from the same table as the Sales. Also, I built and connected an excel file that relates branch number to respective carrying cost.

So my question is:

How can I combine all of these values into one formula that looks like this, (2DS/IC)^(1/2), that is lined to both Item Number and Branch Number?

Should I join them all into one table and apply the formula there?

or

Should I try to write the formula in the expression page of the chart wizard?

or

Is the an alternate location or method in which one can apply mathematical formulas?

Thanks.

[#2]

Hi,

it is nearly impossible to say exactly where to put your expressions.

According to my experiences

- if possible, these calculations should be done in the load-script rather than in charts. It ts almost better "wasting" load-time (at night) as let the users wait while calculating

- try to build a star schema with a facts-table and dimension tables

- QV-tables can be easily linked together with same fieldnames (take care of casesensitiv)

- don't hesitate to prepare incoming data sets while creating views and (or) use SQL, if you are familar with it

- all data, especially not coming from databases (ecxel, csv) can be manipulated via LOAD-Statements with a lot of functions

HtH

Roland

[Anonymous]

Thanks for your response! Thta makes sense about sacrificing load time to make a mre concise program for the end user. I hace also heard a lot about the "star schema with a facts-table and dimension tables" idea, but as of yet have been unable to find a clear description and/or tutorial on this method. What exactly is the star schema?

[#4]

Hi,

the star schema is the favorite type of data modelling in Business Intelligence -Enviroments. In the middle of the schema resides the facts-table with dim-keys (the links to dim-tables) and the facts or also called measures (values, cost-amounts). Around the facts-tables are the dimensions like customer, time-dim, regions, Items, . . .

The internet is full of examples:

http://www.ibm.com/developerworks/rational/library/07/0626_johnson/StyleStarSchema.gif

http://www.ibm.com/developerworks/rational/library/07/0626_johnson/StarSchema.jpg

and so on.

Note:

You don't need a perfect starschema as data modell in QV, but for me it helps you a lot to avoid trouble you may have with other data-modells like in ERP or CRM-Systems.

HtH

Roland

[Anonymous]

Oh wow! That makes sense! Thanks a ton, it's really appriciated.

So one quick question. I don't really know how to phrase it other than by an example. Say you have a well defined star schema and a program that runs well. On the user interface do you allow access to the fact table fields or the dim-keys? For instance, on the first example you provided say you were trying to view sales by customer and geography. If you were to select a customer (say from a list box) would it also associate across both the tables from Customer to Sales to Geography? Thats to say, do the dim keys have an effect on each other or only on the fact table with which theyre connected. If not, then this will not work and you would have to use the fact table field as the keys.

Does my question make sense? Sorry if Im a little scatered, I've pretty much taught myself  what I know about Qlikview and I feel I'm missing some main ideas.

[johnw]

A selection anywhere in your data model will affect every table connected to it directly OR indirectly.  So yes, a selection of customer would affect the fact table (giving you all sales for that customer), and through it the geography table (giving you all geographical locations for those sales).

[#7]

Hi again,

yes, you are on the right turn. I agree with John: try to check this out by creating a simple dim-table (say customer) and a sales-fact table with sales (price, quantity, netvalue, orderdate, . . . ) and link these two tables with an common keyfield (i.e cust_id). After loading a couple or rows (of course with some common key-values) create a listbox with cust_name for example. And a simple bar chart with cust_name as dim and sth like sum(netvalue) as an expression. That's all to start.

RR

[Anonymous]

So one last question. If I have pulled my needed values and have them all in separate tables(because each value is present in a specific table), how do i go about combining them all into one table to apple the formula. In my experience my options are to LOAD data from the DB or to load it from an existing table using the Resident expression. I have thus far been unable to figure out a way to combine values from multiple tables that already exist within the script. My immidiate response is to use a JOIN command, however, as I need to pull data from 5 different tables, I'm not sure this is the most efficient method.

[#9]

Hi bzim,

it is nearly impossible to tell you a way to bring your data all together, as nobody knows the data(-structures). May be you can post us some sample data (best in an example application with some notes) to help you to build a first version of your data modell.

RR

[Anonymous]

Ah, that makes sense. OK so here is a copy of my script and table viewer:

I know this is a lot to look at, but any feedback will help. The script may be poorly written because I was teaching myself as I went, so any feedback on that is greatly appreciated. As for the table layout, I was attempting to create a star schema, however, I'm not sure if I did it right.

My main concern at this point is how to combine all the mentioned variables or fields needed for my EOQ model.

Also, I'm stuggling with calculating Annual Average Demand. I've tried several methods but keep getting errors or incorrect data.

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;

////////////////////////////////////////////////
// EOQ Data
////////////////////////////////////////////////

CarryingCostInline:LOAD * INLINE [
    Business Unit, Carrying Cost
    10, .15
    20, .38
    30, .15
    40, .38
];//carrying cost values based off of data provided by Kurt. Further indepth calculation maybe required

UnitPrice:LOAD Distinct
     [Item Number],
     Avg([Cost]) as [Unit Cost]Resident ItemLedger
     Group By
     [Item Number];
    

SetupTime:Load
     WorkOrder as [Order Number],
     Floor((setuptime/3600)*250) as [Setup Time]
;SQL SELECT
     WorkOrder, setuptime
FROM "JDE_PRODUCTION".DBO.Workorderstatus
Where setuptime > 0
;