I want to add the [No] from the first line (101) to all the other lines .. therefor I thought of making a SQL like this the following.
I Why is it my nested Select doesn’t work ??
or are there a smarter way than what I thought of doing ?
Best answer by rory.smith
Hi,
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.
Check that there are no duplicates in the result. This gives you a lookup table for the main No for Kaede entries
Take the Vendor table from the ODX again and add a relation from your lookup table to the Vendor table over the Kaede column.
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
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.
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:
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.
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.
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 ..
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.
Check that there are no duplicates in the result. This gives you a lookup table for the main No for Kaede entries
Take the Vendor table from the ODX again and add a relation from your lookup table to the Vendor table over the Kaede column.
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
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.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.