Data transformation pretty tough question

Question

I want to select the data and added a new column called 'check' based on the following rules:

for each 'consumer' column, if the consumer doesn't have a master_consumer value (showing 'null' in the master_consumer) AND if its bill_group is 'Consumer' then its 'check' is the same as the value in the 'Consumer_scope' column
for each 'Master_Consumer' column, if it is not null and the number also is in one of the consumer column, if the 'Consumer_scope' is 'out of scope' then all the consumers belong to this 'Master_Consumer' is 'out of scope', for example, number 7 is a master_consumer and a consumer, number 8 and 9 are consumer, although 8 and 9 are 'In Scope', they belong to number 7, so their 'check' value is still 'Out of Scope' same as number 7
if a consumer's product contains 'For Dairy' and this consumer belongs to a master_consumer, all the 'Check' values within the Master_Consumer is 'Out of Scope' My ideal output is as below:

I have created code but very very slow, because this is a sample data, so just wondering could anyone help with it please? Thanks

"I have created code ..." So where is code??? I don't see it. — Eric
– Eric, Commented Oct 11, 2023 at 23:08
Its crazy to say "pretty tough question" because that tells the rest of us absolutely nothing, since we don't know what you base level is, and for some people here they can do it in their sleep. Please try and make your titles actually relevant to the problem. — Dale K
– Dale K, Commented Oct 11, 2023 at 23:36
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Oct 11, 2023 at 23:37
The logic of Master 1 in the output doesn't quite match up my understanding as expressed in English. — shawnt00
– shawnt00, Commented Oct 11, 2023 at 23:39

shawnt00 · Accepted Answer · 2023-10-11 23:26:36Z

2

case
    when Master_Consumer is null then
        case when Bill_group = 'Consumer'
            then Consumer_scope
            else '' -- ??
        end
    when 
        min(case when Master_Consumer = Consumer and Consumer_scope = 'Out of scope' then 1 end)
            over (partition by Master_Consumer) = 1
        or Product like '%For Dairy%'
        then 'Out of scope'
    else Consumer_scope -- ??
end

answered Oct 11, 2023 at 23:26

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data transformation pretty tough question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related