How to get the Flag value by matching two columns?

[Anonymous]

Hi All,

The sample data is attached below. The column "PG Original" is the main column to be considered.

Step 1 - For Countries which have "MOSE Service" in PG original, Should be matched with "MOSE Service" in PG Derived and Flag should be 1, for rest flag should be 0.

Step 2 - For Countries which dont have "MOSE Service" data in PG Original, should be compared by their PG Values & MOSE Service.  (say) For Country "Austria", there is no MOSE Service data. For that country, flag should be 1 for "Drives"(PG Original) & "Drives"(PG Derived) and "Drives"(PG Original) & "MOSE"(PG Derived), and rest as 0. Similary for Motors service.

Country	PG Original	PG Derived	Flag
Austria	Drives Service	Drives Service	1
Austria	Drives Service	MOSE Service	1
Austria	Drives Service	Motors Service	0
Argentina	MOSE Service	Drives Service	0
Argentina	MOSE Service	MOSE Service	1
Argentina	MOSE Service	Motors Service	0
Austria	Motors Service	Drives Service	0
Austria	Motors Service	MOSE Service	1
Austria	Motors Service	Motors Service	1

I need the flag value to be created as shown in the above table. Please provide the solution/approach to resolve this.

Thanks in advance !!

[Samarth_18 · Accepted solution]

Hi @Anonymous 

You can create a custom column with below code:-

FLag =
IF (
    OR (
        Country_data[PG Derived] = Country_data[PG Original],
        Country_data[PG Derived] = "MOSE Service"
    ),
    1,
    0
)

Output:-

Thanks,

Samarth