To homepage To homepage
Solved

Most optimal way to check if Value of Table A, Column A is also in Table B, Column A

  • 24 April 2023
  • 3 replies
  • 61 views
K
Rank
Userlevel 2

I’ve got the following user request but I am not sure what the most optimal solution is, as i can think of many possibilities.

I’ve got 2 Facts Tables:

  • DSA.Fact_Turnover
  • DSA.Fact_TransportOrders

Both Facts contain the ‘Project’ column. The user request is to finally get the Turnover for all projects which are in Fact_TransportOrder. 

What is the most optimal way to get a check column to see if the Project value of Fact_TransportOrder is also present in Fact_Turnover and why? I want to use this filter column to eventually only present him the Turnover for projects which are also present in the Fact_TransportOrder.

I’m really curious, as I already have such solutions, but am not sure if there are more easy TimeXtender functions or tricks which i’m not using currently.

icon

Best answer by Thomas Lind 25 April 2023, 13:52

View original

3 replies

K
Rank
Userlevel 2

My current solution is as follows:

In my Delivery header table I made a TruckDeliveryCheck column which is Fixed 0, and 1 when the transport mode = Truck

I join this TruckDeliveryCheck column on the Facts_Turnover on Facts_Turnover.Project = DeliveryHeader.Project. This way I can see if the Turnover.project has a Truck Delivery on it.

Is this the most optimal way to achieve this goal?

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Kevin

That is how I would have done it as well.

Add a custom field in the source, give it a value when it hits the condition. Add it as a lookup in the other table and you will have a check to make filters on. Instead of adding this field to your semantic table, you could also create a custom yes no or true false table that you then use as a dimension in your semantic model that points at this field.

I just wanted to wait a bit to allow others to come with their suggestion as I am not really using the program in a live setting and others may have had experiences dealing with this.

tld
Rank
Userlevel 1
Badge +1

Not sure what performs best. I usually make a lookup that does a Count from Delivery with join being Project=Project and Transport Mode = ‘Truck’. Then use the Count-field to determine the faith/destination for the fact-record. 

Thomas Duun, Atlytix - https://www.linkedin.com/in/thomasduun/

Reply


Terms and ConditionsCookie settings