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

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

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.

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

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

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.