I want to write a condition here that if items shipped in a case number on two different shipping ID's are same and item condition is new then flag such a case as 1 else 0.

Hello @anshuman.kher ,

I would try this statement:

CASE

WHEN PU_COUNT_DISTINCT(Table2, Table1.ShippingID) > 1 AND Table2.ItemCon='New' THEN 1 ELSE 0 END

Hope this helps.

Best regards,

Julia Bauer

Hi @julia.bauer 

Thanks for your quick response but unfortunately, this does not solve our problem. We are trying to compare the items shipped on shipping ID's of the same case to check if repeat items are being shipped and then applying the new or old filter on the repeat items shipped. Sometimes on 1 shipping ID we have multiple items shipped as well so the same shipping ID gets repeated in the data. Can you please check and help me .

Hi Anyone,

Can someone assist me with the above conditions.

Thanks

Hi Team,

Just following up to see if someone can answer my query

@david.beche12 

Hi Team,

Following up on this thread if anyone can assist with my query

Thanks

Hi Anshuman,

Can you tell me know that what would be the expected output look like for the screenshot you posted?

That will provide more clarity.

Hi Rahul,

Thanks for getting back to me. Below is the snippet of the out with an explanation

Basically the purpose is to call out the same item that is shipped twice on a case id and map it to the item condition wherever the item condition is new.

Explanation:

Since Items sent on both shipping IDs (321 and 323) 

of case 123 are the same and item condition is new at least once then flag the matching situation as 1 and remaining as 0

Hi Team,

Just following up to check if anyone can answer this query

Hey @anshuman.kher ,

I think my query is then not very far away from the solution:

CASE

WHEN PU_COUNT(Table2, Table1.ShippedItems) >1 AND Table2.ItemCon='New' THEN 1 ELSE 0 END

This will aggregate the ShippedItems on the basis of the case number. If this doesn't work for you, however, I think I cannot assist you as it is very hard to suggest a code without having the possibility to try out different variants and if they provide the right solution.

@rahul.sawan12 : Do you have a different solution in mind?

Hello, could you explain what the relation between your tables is? I see 2 tables named Table 2 and 1 table 1 but they seem to have a 1 - 1 relationship, right? how are they connected in the DM?