Fields from multiple tables

Thu, 27 Mar 2014 08:41:56 GMT

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

Re: Fields from multiple tables

Thu, 27 Mar 2014 16:02:14 GMT

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

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

Re: Fields from multiple tables

Thu, 27 Mar 2014 17:56:49 GMT

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

Re: Re: Fields from multiple tables

Fri, 28 Mar 2014 08:59:29 GMT

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

Re: Re: Fields from multiple tables

Fri, 28 Mar 2014 14:39:34 GMT

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.

Re: Re: Fields from multiple tables

Fri, 28 Mar 2014 14:48:23 GMT

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

topic Re: Re: Fields from multiple tables in QlikView

Fields from multiple tables

Re: Fields from multiple tables

Re: Fields from multiple tables

Re: Re: Fields from multiple tables

Re: Re: Fields from multiple tables

Re: Re: Fields from multiple tables