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
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?
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:
and so on.
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.
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.
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.
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.
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
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]
ILDCT, ILMCU, ILDCTO, ILDOCO, ILLITM, ILLNID, ILUNCS, ILLOCN, ILTREX, ILTRDJ, ILTRQT, ILITM
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
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;
Date(Max([Order Date]), 'M/D/YYYY') as CurrentDateResident ItemLedger;
Let vMaxVar = Peek('CurrentDate');
//Step 1 Inventory Calc
Sum([Invoice Key]) as Invoice,
Sum([Work Order Key]) as [Work Order],
Sum([Transfer Key]) as TransfersResident ItemLedger
//// Inventory Info
Sum(Invoice+[Work Order]+Transfers) as [Inventory Change]Resident InventoryTemp
DROP Table InventoryTemp;
//Current Inventory Info
LIITM as [Item Number (Short)],
LIMCU as [Business Unit],
LILOCN as Location,
LIGLPT as [G/L Category],
LIPQOH/10000 As [Qty On Hand]
LIITM, LILOCN, LIGLPT, LIPQOH, LIMCU
where (LIPQOH <> 0);
//Current Inventory Final
[Item Number (Short)],
Sum([Qty On Hand]) as [Total Current Inventory]Resident ItemLocationFile
[Item Number (Short)];
Drop Table ItemLocationFile;
// EOQ Data
CarryingCostInline:LOAD * INLINE [
Business Unit, Carrying Cost
];//carrying cost values based off of data provided by Kurt. Further indepth calculation maybe required
Avg([Cost]) as [Unit Cost]Resident ItemLedger
WorkOrder as [Order Number],
Floor((setuptime/3600)*250) as [Setup Time]
Where setuptime > 0
your star schema looks great: "Itemledger" as FactsTable and some DimensionTables around it. If you like (not necessary), you can join (better world would be "merge", as a join in QV is not the same as a SQL-Join) tables like "UnitPrice" into "ItemLedger" using "Left join (ItemLedger)" at the beginning of the corresponding LOAD-Statement. When joining: Take care of fitting keyvalues in the common keyfield, otherwise you may get a different (and possible incorrect) amount of rows after the join.
And your script isn't poorly written and you did it right.
So you should be able to start with an expression in a chart (let's start with a straighttable to see the results as values).
Any selections should now work and for your maths in your formulas the "coming from" of your fields is independed from any table because your data modell works fine for me.
As a second step check out performance with more rows. May be you need to transfer your maths into script. This is of course better way, but I prefer starting it in charts and transfer the expression whereever meaningful.
So, now it is time for sunday evening meal.
I missed the Branch Number you mentioned in your first post. Is it the "Business Unit" ? :-)