Solved

Lookup between two transaction lists

  • 13 June 2023
  • 9 replies
  • 218 views
K
Rank
  • Karl Budding Pigmenteer
  • Budding Pigmenteer
  • 5 replies

Hi, I have two transaction lists:

TBL_employees - a transaction list that includes information relating to each employee at the end of each month
TBL_accounting - a transaction list that includes all the journal entries from our accounting software


In the TBL_accounting we have a dimension called EMPLOYEE_ID, this dimension contains the ID of an employee for all journal entries that relate to a specific employee (ex. salaries), and a dimension MONTH that contains the month of the journal entry.

In the TBL_employees we have the same two dimensions - EMPLOYEE_id and MONTH as well as a dimension called EMPLOYEE_category which specifies if the employee is a manager or an individual contributor.

What I want to do is add the dimension EMPLOYEE_category into TBL_accounting using the EMPLOYEE_id and MONTH dimensions as filters but I can’t find a formula that works.

The challenge is that the EMPLOYEE_category can vary for the same EMPLOYEE_id depending on the month (ex. an employee being promoted), so I’m not sure if I should use a FILTER or a BY, and how to make it work? I’ve tried the following:

TBL_employees.EMPLOYEE_category
[FILTER: TBL_employees.EMPLOYEE_id = TBL_accounting.EMPLOYEE_id] 
[FILTER: TBL_employees.MONTH = TBL_accounting.MONTH] 

but I get the following error message: Error: Dimension error: the FILTER modifier cannot filter over a Dimension which is already not there. Missing Dimension: EMPLOYEES_application::TBL_employees,TBL_accounting)

Any help would be much appreciated!

icon

Best answer by Franck C 13 June 2023, 12:41

View original

9 replies

Franck C
Rank
Userlevel 4
Badge +4

Hey Karl !

 

Here my advice would be to add a middle step → Create a metric of dimension Employee Category, structured by Month and Employee. It would map for each month, each employee with a category. This metric will probably be usefull anyway. 

You can create this metric with a formula like this one: 

 TBL_employees.EMPLOYEE_category[by firstnonblank: TBL_employees.EMPLOYEE_id,TBL_employees.MONTH] 

Once you have this metric, you can use it in your accounting TL property :

New_Metric[by: TBL_accounting.EMPLOYEE_id,TBL_accounting.MONTH]

Let me know if you have questions !

 

Franck

K
Rank

Hi Franck! Thanks for the reply, unfortunately when I try the formula you suggested I get the following error:

Error: Type error: formula type and target type are not compatible. Formula type: Dimension ('Shared Dim'::'EMPLOYEE_category'), Target type: Number.

I suspect this is because the EMPLOYEE_category dimension contains text values (Manager, Individual Contributor etc.) rather than number values?

K
Rank

I’ve managed to resolve the above issue by changing the metric data type to dimension > EMPLOYEE_category. However once I try map it to TBL_accounting using the formula you suggested I get the following error message:

Error: Dimension error: formula Dimensions and current Block Dimensions are not compatible. Can't implicitly remove 'Shared Dim'::'Employee category' Dimension(s) on non numerical Metric.

Any idea of what might be causing this?

Franck C
Rank
Userlevel 4
Badge +4

This last calculation is performed in a property of your accounting TL ? If yes, this property should be of dimension Employee Category. 

 

If still an issue, can you share the formula you put inside your property ?

 

Thanks

K
Rank

Hi Franck, the column in the TL TBL_accounting has the following attributes:
Type: Dimension
Dimension: EMPLOYEE_category

I then inserted the formula you provided into the formula bar:

New_Metric[by: TBL_accounting.EMPLOYEE_id,TBL_accounting.MONTH]

which returns the following error:
Error: Dimension error: formula Dimensions and current Block Dimensions are not compatible. Can't implicitly remove 'Shared Dim'::'Employee category' Dimension(s) on non numerical Metric.

Franck C
Rank
Userlevel 4
Badge +4

And can you share the structure of your new metric ?

K
Rank

Sure:

Data type: Dimension > EMPLOYEE_category
Dimensions: EMPLOYEE_id, EMPLOYEE_category, MONTH

Franck C
Rank
Userlevel 4
Badge +4

Here your metric should not have ‘employee category’ in the structural dimension. As described earlier, “Create a metric of dimension Employee Category, structured by Month and Employee”.

 

Once you have this aligned it should be ok :) 

K
Rank

legend, works perfectly!

Reply


Ask your question here!

Terms & ConditionsCookie settings