Combine two data set if the certain condition met.

[akumar2609]

I need help to combine two different data set based on the condition. Condition applies on scenario column. If there is “A” scenario available in Data set 1 then, combine data set 1 and 2 (we will always scenario A in data set 2) and if condition do not met, then just gave me the data set 1.

Data set 1
Scenario	Q1	Q2	Q3	Q4
A	123	234	345	456
B	2876	9870	9754	953
A	9087	5432	9085	1234
C	0987	5643	222	231
D	908	124	876	457
D	896	6754	4312	097

Data set 2
Scenario	Q1	Q2	Q3	Q4
A	564	234	345	456

First Condition - In this case, we have A scenario in data set 1, then my required output will be

Scenario	Q1	Q2	Q3	Q4
A	123	234	345	456
B	2876	9870	9754	953
A	9087	5432	9085	1234
C	0987	5643	222	231
D	908	124	876	457
D	896	6754	4312	097
A	564	234	345	456

Second Condition- A scenario is available in Data set 2 but not in data set 1, then just give me the data set 1 as output.

Data set 1

Scenario	Q1	Q2	Q3	Q4
B	123	234	345	456
B	2876	9870	9754	953
F	9087	5432	9085	1234
C	0987	5643	222	231
D	908	124	876	457
D	896	6754	4312	097

Data set 2
Scenario	Q1	Q2	Q3	Q4
A	564	234	345	456

Required output if second condition met.

Scenario	Q1	Q2	Q3	Q4
B	123	234	345	456
B	2876	9870	9754	953
F	9087	5432	9085	1234
C	0987	5643	222	231
D	908	124	876	457
D	896	6754	4312	097

[PhilipMannering]

Hi @akumar2609 

Does this sort of thing do what you need?

[SGolnik]

You can add filter tools with an if-then statement to check to see if it meets your condition, followed by a union tool. If it doesn't meet your condition nothing will flow from the second input into the union, but if you meet the condition, the two datasets will union together. See the attached sample of how this can work. Another more complex way to handle this would be to use the Detour tool.

[akumar2609]

Thank you for the quick response; however in your output, data is getting duplicated.

Scenario               Q1

A                             564

B                             2876

A                             123

B                             2876

A                             9087

C                             0987

D                             908

D                             896

A                             564

[akumar2609]

Thank you for the reply...
Your output is not as per my requirement. 

If in my first dataset, there is A scenario available, then append data set 1 & @2. Note - We may have the same scenario repeated multiple times. For example 

Data set 1

Scenario	Q1	Q2
A	123	478
A	345	555
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

Data set 2

Scenario	Q1	Q2
A	870	999

As in the above example, we have A in Data set 1, my output should be Data set1 + data set 2 (i.e.)

Scenario	Q1	Q2
A	123	478
A	345	555
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987
A	870	999

Note- In data set 2, we will always have only A scenario. 

In case in my first data set, A scenario is not available, then my output should be just Data set 1.

For example 

Data set 1

Scenario	Q1	Q2
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

Data set 2

Scenario	Q1	Q2
A	870	999

As in this case, A scenario is missing in Dataset 1, so my output should be just data set 1

like this 

Scenario	Q1	Q2
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

[akumar2609]

Thank you for the quick response; however in your output, data is getting duplicated.

Scenario               Q1

A                             564

B                             2876

A                             123

B                             2876

A                             9087

C                             0987

D                             908

D                             896

A                             564

[akumar2609]

Thank you for the reply...
Your output is not as per my requirement. 

If in my first dataset, there is A scenario available, then append data set 1 & @2. Note - We may have the same scenario repeated multiple times. For example 

Data set 1

Scenario	Q1	Q2
A	123	478
A	345	555
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

Data set 2

Scenario	Q1	Q2
A	870	999

As in the above example, we have A in Data set 1, my output should be Data set1 + data set 2 (i.e.)

Scenario	Q1	Q2
A	123	478
A	345	555
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987
A	870	999

Note- In data set 2, we will always have only A scenario. 

In case in my first data set, A scenario is not available, then my output should be just Data set 1.

For example 

Data set 1

Scenario	Q1	Q2
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

Data set 2

Scenario	Q1	Q2
A	870	999

As in this case, A scenario is missing in Dataset 1, so my output should be just data set 1

like this 

Scenario	Q1	Q2
B	567	458
C	879	890
D	807	753
D	568	234
F	234	987

[binuacs]

@akumar2609 one way of doing this