Hi,

Best practice would be to use fixed values in combination with conditions. Just add [No] as a customer measure in your field, and add test2 and test 1 as a fixed value transformation. After that apply condition based on when that specific outcome should be applied.

You can add conditions by means of expading the transformation (or fixed value) click Conditions. The Conditions task pane appears.

• Click on a Field in the pane.
• In the Operator list, click the operator you want to use.
• Click Value and enter a value to use in the comparison in the box

In the end it should look something similar like this:

Hope this helps!

Kind regards,

Devin

Hi,

Best practice would be to use fixed values in combination with conditions. Just add [No] as a customer measure in your field, and add test2 and test 1 as a fixed value transformation. After that apply condition based on when that specific outcome should be applied.

 

You can add conditions by means of expading the transformation (or fixed value) click Conditions. The Conditions task pane appears.

• Click on a Field in the pane.
• In the Operator list, click the operator you want to use.
• Click Value and enter a value to use in the comparison in the box

In the end it should look something similar like this:

 

Hope this helps!

Kind regards,

Devin

I actually tried that you are mentioning but that doesn’t cover my scenario completely .. but I actually got it to work .. ALMOST .. the only problem is now that I always get the top1 record in the example below .. 

But the problem with the above example is that in the iKæde] = �Kæde] it doesn’t take the tKæde] from that particluair row .. it always gives me the top 1st row that meets the condition .. 

I thought it would check:
Kæde] = “example”
then it would find alle the rows that has the “example” parameter in aKæde] and after that find the one in rMain chain vendor_sp_jcd] that equals “true” .. 
and when it came to a row where wKæde] = example2 then it would find all the rows with “example2” in Kæde] .. 

In other words, I thought it would “pass” the rKæde] from the “CASE” statement .. 
What is it i’m not seeing/figured out :-D
 

Don't fully understand your issue, an example of the in- and output would help. But I can imagine that you need to aggregate here. Just have a look into SQL's partition by funtion.

To be honest, I still think this is not the "best” solution and you should be able to (at least) partially create this with conditions.

Hi,

while you could do what you want by using a SQL Window function, what you should actualy do is first create a derived aggregate table that filters on �Main chain vendor_sp_jcd] = true and has the �Kæde] per key. Subsequently, extract the table a second time to DWH and look up the  eKæde] from your aggregate table.

to @devin.tiemens  and @rory.smith 

I tried to aggregate the table and I might be doing something wrong, but it didn’t give me the result I needed.

 

This is the data .. 
If there is a parameter in “Kæde” then → I must find the row in the same “Kæde” that has “Main Chain Vendor_SP_JCD” as true and take that’s “No” and put in “PriceListVendorID” .. 
If there is no “Kæde” it should have it’s own “No” in “PriceListVendorID” .. 

I hope it makes sense what I’m trying to achieve .. 

Hi,

1. Take the table with No , Kaede, and optionally “Main Chain Vendor_SP_JCD” and add a data selection rule on “Main Chain Vendor_SP_JCD” = true. 
2. Check that there are no duplicates in the result. This gives you a lookup table for the main No for Kaede entries
3. Take the Vendor table from the ODX again and add a relation from your lookup table to the Vendor table over the Kaede column.
4. Drag the No from the lookup table to the second Vendor table. Add a join or condition on “Kaede” is not empty. This means it will look up the No if there is a Kaede, you don't need to look at the “Main Chain Vendor_SP_JCD” because it will find the correct value regardless
5. Add a transforation to the lookup that picks up the value from No in the Vendor table itself if the lookup is empty

This pattern is very common in that source system and can be implemented without any SQL.

I finally figured it out with your help @rory.smith  .. thank you very much for taking your time to help me ..