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:
Look if column Route Bron is empty, If Route Bron is not empty, just show Route Bron.
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!
Page 1 / 1
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 @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 @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.
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.
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.
