topic Connecting Tables in Connectivity & Data Prep

Connecting Tables

notlohdahc — Tue, 18 Feb 2025 22:14:39 GMT

I may be overthinking this so any guidance is appreciated.

I have these tables

- Planned $ by salesperson # and Product Group

- Sales history which contains Customer #s, Products, but NOT Product Group

- Customer master which contains Customer #, and Salesperson #

- Product Master which contains Product # and Product Group

We have a Plan tab on the dashboard where they want to add "Product Group" to the selection group.

We are using linked tables in the scripts behind the dashboard for current data connections. I can't figure out how to get these tables linked to summarize Sales $ by Product Group.

I know this probably not nearly enough information, but any help would be appreciated.

Re: Connecting Tables

chriscammers — Tue, 18 Feb 2025 23:01:49 GMT

Create a table for your Product Groups( Key and Description fields)
Create a table for your Salesperson (Key and Description fields)
When you load your Sales History map the Product Group Key based on the product and the Salesperson Key based on the Customer
When you load your Plan data Concatenate it into the same table as your sales history

There will be cases where selections will make the plan data "disappear" in that case you have decide what you want the dashboard to do, you can ignore selections on the Product or custome tables or just let the measures go blank.

I find that linked tables are a ton of effort while concatenated fact tables are simple and have better results especially when you get to even more complex scenarios.

Re: Connecting Tables

notlohdahc — Tue, 18 Feb 2025 23:10:25 GMT

Thank you for the quick response. I'll see what I can do.

I'll respond next week sometime.

Re: Connecting Tables

notlohdahc — Wed, 05 Mar 2025 21:54:02 GMT

I've simplified the tables structures to hopefully make this easier. However I can not get my Sales $ to appear in the Plan tab summarized by Product Group. I can however get the Plan $ to appear by Prod Group.

I added the Salesperson # and Product Group to the sales history fact table. I was thinking by doing so I'd just need to link my Plan Fact table to the Sales History fact table. But it's not working as expected, I'm getting $0 in the Sales column.

here are my Link Tables

LinkTable:
LOAD Distinct
OrdersRowNo as %OrdersKey,
[Date Invoiced] as %DateKey,
[Order Reporting Site Num] & '|' & Component as %ProductKey,
[Order Reporting Site Num] & '|' & [SoldTo Cust] as %CustomerKey,
Site as %SiteKey,
[Site] & '_' & [Component] as %key_CNSRLOC,
[Order Reporting Site Num] as %ReportingSiteKey,

[Date Invoiced] & '|' & SubField([Slsprsn Territory], '-', 1) & '|' & [Orders Product Group] as %PlanPGKey
FROM
$(vApp_QVDPath)\Municipal Orders.qvd
(qvd);

Concatenate(LinkTable)

LOAD Distinct

PlanPKey as %PlanKey,

[Plan Date] as %DateKey,

'P_' & PlanPKey as %CustomerKey, // see Customer table for special concatenation

[Plan Reporting Site Num] as %ReportingSiteKey,

[Plan Date] & '|' & [Plan Salesperson Num] & '|' & PlanProdGroup as %PlanPGKey

FROM

$(vApp_QVDPath)\MunicipalPlanFYs.qvd

(qvd);

Re: Connecting Tables

marcus_sommer — Thu, 06 Mar 2025 12:52:36 GMT

Keep it simpler - like suggested from @chriscammers and avoid the link-table stuff. Just concatenating the fact-tables by harmonizing all field-names and data-structures as much as possible respectively suitable.

Important is the understanding that sales and plans are in general the same data - only with a differences in the point of view. That they have a different granularity is quite common and usually no problem because a missing categorization like the product-group could be simply mapped or the global value might be distributed to the single products - the same with periods to dates and so on.

How many efforts it may need depends on the final requirements but regardless to it - no other way will be simpler or saving more efforts.

Re: Connecting Tables

notlohdahc — Wed, 02 Apr 2025 13:16:34 GMT

That great that you all are pro-concatenate but the dashboards I'm working on are 15+ years old and the logic within them is well established using Linked Tables. Thanks for the suggestions but I figured it out keeping the linked tables processes in place.