Trying to add a new custom field based on an already exsisting value

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

devin.tiemens wrote:

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 [Kæde] = [Kæde] it doesn’t take the [Kæ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 [Kæde] and after that find the one in [Main chain vendor_sp_jcd] that equals “true” .. 
and when it came to a row where [Kæde] = example2 then it would find all the rows with “example2” in [Kæde] .. 

In other words, I thought it would “pass” the [Kæ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  [Kæ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 .. 

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