To homepage To homepage
Solved

Optimize loading times for multiple self lookups as a flash fill NULLS solution

  • 9 June 2023
  • 6 replies
  • 77 views
K
Rank
Userlevel 2

v20.10.35.64

A while ago I was looking for a solution on how to conditionally fill down/flash fill values with certain criteria. Unfortunately the answers in this ticket didn't do the trick for me, as I kept getting NULL values with all solutions I've tried. How can I conditionally fill down/flash fill NULL values with previous values based on certain criteria? | Community (timextender.com)

Now I seem to get it working with a Self Lookup view and a Lookup Field (Top first-non value). However, I have to repeat this process for almost 10 columns. The loading of this table went from 06:00 to 30:00 minutes. The lookup field looks as follows. What it does is:

  1. Look if column Route Bron is empty, If Route Bron is not empty, just show Route Bron.
  2. If Route Bron is empty, lookup the TOP first non empty Route Bron Value with the same Source, Project, Cardcode where project is not empty and not 999999

 

Is there a way how I can optimize this process? Thanks in advance!

icon

Best answer by Thomas Lind 9 June 2023, 15:02

View original

6 replies

daniel
Rank
Userlevel 4
Badge +5

Dear @KCMT ,

Maybe it helps if you cut up the transformations into different columns first and then combine them in one final column?

So have one column ith Route Bron, one column with the Route Bron LOOKUP and one final with with the CASE WHEN.

What also might work is writing the whole table ands transforms in a View. Mind you that this is not my preferred solution, but if performance is important then you might have no choice. I would write a CTE load both tables, then joining them and doing the transformation.

Hope this helps

= Daniel

 

Thomas Lind
Rank
Userlevel 5
Badge +5

Hi @KCMT 

Do you use multiple Lookup fields in the table you do this for? If so do they also use TOP 1 as the rule or is it group by?

Mixing the methods will give performance issues they should all be the same type and in general TOP is slower than group by.

Besides that, if you can’t gain performance by using Lookups in a table, making views is the way to do it.

K
Rank
Userlevel 2

Hi @KCMT 

Do you use multiple Lookup fields in the table you do this for? If so do they also use TOP 1 as the rule or is it group by?

Mixing the methods will give performance issues they should all be the same type and in general TOP is slower than group by.

Besides that, if you can’t gain performance by using Lookups in a table, making views is the way to do it.

Hi Thomas, I use multiple lookup fields which all use the TOP1 rule. I guess I will need to have a look at using it in a view then.

Thomas Lind
Rank
Userlevel 5
Badge +5

Yeah unless someone figures a way to do it with group by lookups. I can only see it as being more work an a lot of extra tables containing the previous values.

I wrote this guide that explains how I used the Lead and Lag functions for improved speed using currency rates.

https://legacysupport.timextender.com/hc/en-us/articles/115005972746-Improve-execution-times-by-splitting-exchange-rates-out-on-dates

Christian Hauggaard
Rank
Userlevel 5
Badge +5

Hi @KCMT Were you able to optimize the process you describe? If so could you please help us by selecting a best answer above. If you have any follow up questions please let us know

K
Rank
Userlevel 2

I’ve made it work the following way.

Step 1: Create a view from the table with the columns you want to flash forward and the join columns. Make sure the From table parameter is marked as a Transformation view variant.

Step 2: In the table, create a Conditional Lookup field based on the desired join columns and select ‘Take the first non-empty value’ 

Step 3: Use the Top operator for the lookups:

Step 4: I put the column I want to flashfil as a default value when this value is not empty. The end result looks like this:

Step 5: When deploying you may need to disable differential/managed deployment for it to work to avoid the circular relationship or something.

Step 6: Add your own conditions or transformations where necessary.

Reply


Terms and ConditionsCookie settings