10 Replies Latest reply: Aug 19, 2012 11:21 AM by Roland Kunle RSS

    Where's the best place to manipulate data?

      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.

        • Re: Where's the best place to manipulate data?

          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

          • Re: Where's the best place to manipulate data?

            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.

              • Re: Where's the best place to manipulate data?
                John Witherspoon

                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).

                • Re: Where's the best place to manipulate data?

                  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

                • Re: Where's the best place to manipulate data?

                  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.

                  • Re: Where's the best place to manipulate data?

                    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
                    ;

                     

                    export.png

                     


                      • Re: Where's the best place to manipulate data?

                        Hello again,

                         

                        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.

                        RR

                        P.S.

                        I missed the Branch Number you mentioned in your first post. Is it the "Business Unit" ? :-)