Need Output based on logic given below

[rammuthiah]

DATA_1:

Load * Inline [

Supplier, C1, C2,

A, 100, 100,

A, 200, 200,

A, 300, 250,

];

DATA_2:

Customer, C1, C2,

Load * Inline [

B, 300, 300,

B, 250, 250,

B, 600, 600,

];

I need the logic based on the Scenario 1 and 2:

Scenario 1
Supplier/Customer	C1	C2	Check 1	Check 2	Check(if isnull(Check1),Check 2,Check 1))	Score
A	100	100	TRUE		TRUE	FALSE
A	200	200	TRUE		TRUE	FALSE
A	300	250	FALSE		FALSE	FALSE
B	300	300		TRUE	TRUE	FALSE
B	250	250		TRUE	TRUE	FALSE
B	600	600		TRUE	TRUE	FALSE
Scenario 2
Supplier/Customer	C1	C2			Check	Score
A	100	100			TRUE	TRUE
A	200	200			TRUE	TRUE
A	300	300			TRUE	TRUE
B	300	300			TRUE	FALSE
B	250	250			TRUE	FALSE
B	600	500			FALSE	FALSE

[sunny_talwar]

Which column are you looking to calculate?

[rammuthiah]

Score Column. Score result based on Supplier/Customer column.

[sunny_talwar]

So, couple of questions

1) What is the logic behind the outputs you are seeing in the two scenarios?

2) Why 2 scenarios? You need two tables with different logics?

[ashwinishinde]

Try this for first scenerio:

test:

load *,

if(C1=C2,'True','false') as check1,

'Supplier' as Flag1;

Load * Inline [

Supplier, C1, C2,

A, 100, 100,

A, 200, 200,

A, 300, 250,

];

Concatenate

DATA_2:

load *,

if(C1=C2,'True','false') as check2,

'Customer' as Flag2;

Load * Inline [

Supplier, C1, C2,

B, 300, 300,

B, 250, 250,

B, 600, 600,

];

load *,

if(IsNull(check1),check2,check1) as Result

Resident test;

drop table test;

[ashwinishinde]

[rammuthiah]

Value for the table is from 2 tables. For instance Cost from supplier 1 and supplier 2

Actual Supplier1 - 80

Actual Supplier2 - 60

We have a target for supplier

Target for Supp 1 : 70

Target for Supp 2 : 70

Comparing those results in Check 1 for A and Check 2 for B. We can’t bring those in a single. So that I have used “Check(if isnull(Check1),Check 2,Check 1))” to bring all values in Single column.

Say like Supp1 is main, if it fails to achieve the task, then both will result in Fail.

But Supp 2 is comes under of Supp1, if it fails to achieve the task, but not Supp1, will result in Pass category

[rammuthiah]

I need this in a single table

[rammuthiah]

Kindly scroll to the right side of my requirement