Hi Team - Im trying to capture details from a field based on different scenarios. In sample data below, From Field3 Scenario1. Need to capture '1234' , Scenario-2. If Inv_Num is null or not available then need to capture Ord_Num value '1234', Scenario-3. If Field3 is blank then need to consider Field2. Field1Field2Field3Prod1Inv_NumA,B,C,Inv_Num,1234,F,GProd2Inv_NumOrd_Num,1234,C,Inv_Num,null,F,GProd3Inv_NumOrd_Num,1234,C,D,E,F,GProd4Inv_NumNull(Select Field2(Inv_Num) Field3 is different for each line item, its based on Product in Field1. Tried different things but couldnt get a solution, Any help will be much appreciated.

Capture details based on conditions

Hi Team - Im trying to capture details from a field based on different scenarios.

In sample data below, From Field3 Scenario1. Need to capture '1234' , Scenario-2. If Inv_Num is null or not available then need to capture Ord_Num value '1234',

Scenario-3. If Field3 is blank then need to consider Field2.

Field1	Field2	Field3
Prod1	Inv_Num	A,B,C,Inv_Num,1234,F,G
Prod2	Inv_Num	Ord_Num,1234,C,Inv_Num,null,F,G
Prod3	Inv_Num	Ord_Num,1234,C,D,E,F,G
Prod4	Inv_Num	Null(Select Field2(Inv_Num)

Field3 is different for each line item, its based on Product in Field1.

Tried different things but couldnt get a solution, Any help will be much appreciated.

Best Practices

Accepted answers

caltang

Try this:

For ANARK.yxmd

All comments

caltang

Your scenarios are not very clear, can you explain greater detail please?

In any case, whilst waiting for your explanation, I've done some prep work for your data. Since Field_3 is delimited by commas, you can split it by Row. This helps you group them up or even use a multi-row formula to achieve what you want.

For ANARK.yxmd

ANARK

Thanks for your response. Have around 5M rows and multiple products. So not sure if split it by Row will work.

Field3 is different for each line item, its based on Product in Field1. So for each Product the values in Field3 will be of different length, sometimes it will be blank and based on that need to capture a different item in the same field3 or Field2.

From Field3,

Scenario1. Will have to check if Inv_Num is there in field\string, if available Need to capture '1234' which is next item to Inv_Num

Scenario-2. If Inv_Num is available but next item is null then need to check for Ord_Num and capture next value '5678'

Scenario-3. If Inv_Num is not at all available then need to check for Ord_Num and capture next value '5432'

Scenario-3. If Field3 is completely blank\null then need to consider value in Field2 which is Inv_Num4

Also values in Field2 its not available in Field3, not trying to lookup.

Current:

Field1	Field2	Field3
Prod1	Inv_Num1	A,B,C,Inv_Num,1234,F,G
Prod2	Inv_Num2	Ord_Num,5678,C,Inv_Num,null,F,G
Prod3	Inv_Num3	Ord_Num,5432,C,D,E,F,G
Prod4	Inv_Num4	Null

Expected: Ord\Inv Num is my expected output field

Field1	Field2	Field3	Ord\Inv Num
Prod1	Inv_Num1	A,B,C,Inv_Num,1234,F,G	1234
Prod2	Inv_Num2	Ord_Num,5678,C,Inv_Num,null,F,G	5678
Prod3	Inv_Num3	Ord_Num,5432,C,D,E,F,G	5432
Prod4	Inv_Num4	Null	Inv_Num4

caltang

Now... in your original post, you did not mention that you have 5M rows... if you had, then my initial prep would not have been done. It will make it larger than it needs to be. Thanks for the details, I'll have a look and see how we can do this better.

caltang

Try this:

For ANARK.yxmd

ANARK

Thanks, This really helps. Let me try to implement.

caltang

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

ANARK

Thanks Again, was able to implement the logic.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703