how to filter out invalid field combinations?

Table-valued functions in finaquant .net libraries

how to filter out invalid field combinations?

Postby LeonarK » 18 Sep 2014, 11:58

Hi,
I installed your recent excel addin for table functions. It works so far, however, I couldn't find out how to generate a test table for our cost centers. The case is like this:

We have cost centers, business units and products/services associated with cost centers. A service/product is associated with every cost center, but a business unit is not associated with every cost center. A business unit is normally associated with only some of the cost centers.

My question: How can I filter out invalid business unit - cost center combinations if i generate a test table with the table function named Combinate Fields in Excel?

Thanks in advance for any help
LeonarK
 
Posts: 3
Joined: 11 Dec 2013, 23:22

Re: Use table function named Combinate Table Rows

Postby tunc » 09 Dec 2014, 11:48

Hello Leonark

So, you have 3 attributes:
1) Product
2) CostCenter
3) BusinessUnit

As I understand, you have N-to-N relation between Product and CostCenter (i.e. every Product is associated with every CostCenter), but a hierarchical 1-N relation between BusinessUnit and CostCenter.

You can generate your test table containing all the three attributes above by using the table function named Combinate Table Rows.

Required steps:
1) Create the hierarchical table with the fields CostCenter and BusinessUnit manually. You need to create this table manually because you know which CostCenter belongs to which BusinessUnit.

2) Create a product table with the single field Product containing all possible product names as attribute values.

3) Combinate the rows of these two tables by using the table function Combinate Table Rows.

These steps are depicted with the images below:

Create CostCenter (TCostCenterTbl) and Product (TProductTbl) tables
Image

Combinate rows of tables with the table function named Combinate Rows
Image

Resultant test table after combinating rows:
Image

Note that in the resultant test table all products are related with all cost centers, whereas cost centers are related only with the correct business units.

Tunc
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Re: how to filter out invalid field combinations?

Postby LeonarK » 15 Dec 2014, 17:03

Excellent, thanks.
LeonarK
 
Posts: 3
Joined: 11 Dec 2013, 23:22


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest