Fields from multiple tables

[#1]

Hi

I have tried this several ways with mixed results (perhaps because some methods were done incorrectly?).

If you refer to the attached image, you will see my table structure.  In my model I have a formula that uses all the circled fields.  Currently this formula is being calculated in the 'front end'.  I know this is less than ideal due to the sheer amount of data in the various tables.

Previously I had used joins and mapping tables to create a single table during the load script - making the formula simple and adding it to that table using a resident load.  However, on the whole this looked like it had worked but on closer inspection seemed to be giving some strange results, perhaps my joins were wrong.

Currently all the tables are separate. 

How can I move the formula back into the load script?

Oli

[#2]

Can anyone suggest what the best way of doing this is?

(Sorry, I don't mean to sound impatient)

[#3]

Hi there,

Is FactssalesOrderTable  something that you created?( by various joins/concatenation)

Its tough to say just by looking at this, but one thing to notice while joining is that it will cause duplicate records in the resultant table if not done with caution.

Its not absolutely mandatory to do all the calculations on the back-end, but its good to do whenever possible. But in some cases you might have to calculate on the front-end.

A few things you can do for front-end calculations:

• Minimize usage of if(), count(Distinct  ....)
• Set analysis should help you in all those cases
• Create flags on the back-end and use them in the front-end expressions wherever applicable

These are just a few points to get you moving in the right direction.

Hope it helps

Thanks

AJ

[#4]

Hi Ajay

Thank you for your time.  I have put a section of the code below.  The only thing I could think of was load all the data and then do a couple of resident loads at the end of the script to pull in the fields from the other tables...?  This seems long winded for the reload but may save much more time for front end calculations.  Especially as the formula I am trying to move to the load is a large IF() and the results are used throughout the front end...

I am eager to learn so if you notice any horrible coding mistakes below please feel free to point those out also.

// ******************************************************** ************************ \\

[maptblSupplierHierarchy_number]:

mapping LOAD

    MS1SUP,

    MS1MST

;

SQL

SELECT

    MS1SUP,

    MS1MST

FROM

    DATALIBR.BUYMS1PF

;

// ******************************************************** ************************ \\

[maptblSupplierHierarchy_name]:

mapping LOAD

    [Buying Master Supplier Number Link],

    [Buying Master Supplier Name]

FROM

    $(ServerPath)BUYMSTPF_Supplier Info.qvd(qvd)

;

// ******************************************************** ************************ \\

[dimCustomers]:

LOAD

    [Account Link]                                            as %Account,

//

    [Account Master Account Number]                            as [Account no],

    if(match(left([Account Master Account Number],1),'£'),

        dual('True',1),

        dual('False',0))                                    as IsConsignmentAcc_Flag,

    [Account Master Branch]                                    as [Account branch no],

    if([Account Master Branch] = Repeat(Chr(32),6),

        [Account Master Account Number],

        [Account Master Account Number] & '-' & [Account Master Branch])

                                                            as [Account & Branch],

    [Account Master Account Name]                            as [Account name],

    if(exists(Acc,[Account Master Account Number]),

        dual('True',1),

        dual('False',0))                                    as IsFOBCustomer_flag,

    [Account Master Country]                                as [CountryCode]

FROM

    $(ServerPath)ACCMSTPF - Account Master.qvd(qvd)

;

// ******************************************************** ************************ \\

[factSalesOrder_Hdr]:

LOAD

    autonumber([Order Link],'%OrderID')                        as %OrderID,

    [Account Link]                                            as %Account,

//   

    [Order Link]                                            as [Order no],

    if([Order Header Order Status] = 5 or [Order Header Order Status] = 6,

        dual('True',1),

        dual('False',0))                                    as IsCompletedOrder_Flag,

    1                                                        as [# Count of sales orders]

FROM

    $(ServerPath)Sales_Order_Header_recent.qvd(qvd)

;

FOR n = 2 to 2

    // ******************************************************** ************************ \\

    LOAD

        autonumber([Order Link],'%OrderID')                        as %OrderID,

        [Account Link]                                            as %Account,

    //

        [Order Link]                                            as [Order no],

        if([Order Header Order Status] = 5 or [Order Header Order Status] = 6,

            dual('True',1),

            dual('False',0))                                    as IsCompletedOrder_Flag,

        1                                                        as [# Count of sales orders]

    FROM

        $(ServerPath)Sales_Order_Header_$(vYear$(n)).qvd(qvd)

    ;

NEXT n

LET n                                                         = nothing;

// ******************************************************** ************************ \\

[factSalesOrder_Ln]:

LOAD

    autonumber([Order Link],'%OrderID')                        as %OrderID,

    [Order Item Stock Number]                                as %StockNo,

    autonumber([Order Item Invoice Date],'%InvDateID')        as %InvDateID,

//

    [Order Item Invoice Date]                                as [Invoice date],

//    [Order Item Stock Number]                                as StockNo,

    [Order Item Stock Box Qty]                                as [Stock box qty],

    [Order Item Quantity Shipped]                            as [Qty shipped],

    [Order Item Line Value]                                    as [line value],

    [Order Item Product Code 3]                             as [Product code @ order],

    [Order Item Made Up]                                    as [Made up],

    [Order Item Credit Type]                                as [Credit type],

    [Order Item Average LDP]                                as LDP_Avg,

    [Order Item Projected LDP]                                as LDP_Prj

FROM

    $(ServerPath)Sales_Detail_History_recent.qvd(qvd)

WHERE

    [Order Item Product Code 3]                             = 924

;

// ******************************************************** ************************ \\

FOR n = 2 to 2

    LOAD

        autonumber([Order Link],'%OrderID')                        as %OrderID,

        [Order Item Stock Number]                                as %StockNo,

        autonumber([Order Item Invoice Date],'%InvDateID')        as %InvDateID,

//

        [Order Item Invoice Date]                                as [Invoice date],

//        [Order Item Stock Number]                                as StockNo,

        [Order Item Stock Box Qty]                                as [Stock box qty],

        [Order Item Quantity Shipped]                            as [Qty shipped],

        [Order Item Line Value]                                    as [line value],

        [Order Item Product Code 3]                             as [Product code @ order],

        [Order Item Made Up]                                    as [Made up],

        [Order Item Credit Type]                                as [Credit type],

        [Order Item Average LDP]                                as LDP_Avg,

        [Order Item Projected LDP]                                as LDP_Prj

    FROM

        $(ServerPath)Sales_Detail_History_$(vYear$(n)).qvd(qvd)

    WHERE

        [Order Item Product Code 3]                             = 924

    ;

NEXT n

LET n                                                         = nothing;

(Many) thanks again

Oli

[#5]

Its really tough to say just by looking at this, but it seems alright to me.

Not sure why you need that For loop though.

[#6]

Lol - that loop was from 2 to 5 but due to a recent change I was lazy and nullified it instead of removing it