Matching Two Coloumns

[#1]

i have two columns in excel format i want to check whether one coloumn name is matching with other or not and if it is matching then on which coloumn no. is that data on

---------------------------------------------------------

Name

navneet

balraj

nikita

alkesh

rocky

subodh

---------------------------------------------------

First Name

balraj

nikita

navneet

rocky

alkesh

-----------------------------------------------

i WANT LIKE THIS

namecheck

yes-3

yes-1

yes-2

yes-5

yes-4

no

[anbu1984]

Name:

LOAD Name

FROM

[Matching column.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Name)

First_Nm:

LOAD [First Name] As Name, RowNo() As RowNum

FROM

[Matching column.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load If(IsNull(RowNum),'No','Yes-' & RowNum) As MatchingDetails Resident Name;

[jagan]

Hi,

Try like this

DataTemp:

LOAD

DISTINCT Name

From DataSource;

LEFT JOIN(DataTemp)

LOAD

DISTINCT [First Name] AS Name, RowNo() AS Num

From DataSource;

Data:

LOAD

Name,

If(Len(Num) >0, 'Yes-' & Num, 'No') AS namecheck

Resident DataTemp;

DROP TABLE DataTemp;

Regards,

Jagan.

[SunilChauhan]

see the attched file

hope this helps

[its_anandrjs]

New Updated script now check

Do simpler way load your two tables and left join on the basis or the Name field see the code below

//////////////////////////

tmp:

LOAD * Inline

[

Name

navneet

balraj

nikita

alkesh

rocky

subodh];

Left Join

LOAD [First Name] as Name,[First Name],RowNo() as Rid;

LOAD * Inline

[

First Name

balraj

nikita

navneet

rocky

alkesh];

Final:

LOAD

Name,

Rid,

If(Name = [First Name],'Yes-'&Rid,'No') as namecheck

Resident tmp Order By Name asc;

DROP Table tmp;

/////////////////////////////

And then take a table box and plot fields

Name

namecheck

Regards

[its_anandrjs]

Hi,

If you get the correct answer from the thread so mark the thread as select appropriate answer as correct / Helful for references.

Regards

[maxgro]

a different one with map

map:

Mapping load [First Name], 'yes-' & recno();

LOAD * Inline

[

First Name

balraj

nikita

navneet

rocky

alkesh];

result:

LOAD Name, ApplyMap('map', Name, 'no') as namecheck

Inline

[

Name

navneet

balraj

nikita

alkesh

rocky

subodh];

DROP field Name;

[Anonymous]

I would prefer to use the Apply Map funtion for this.

Try something like this:

List2:

load * inline [

Name

balraj

nikita

navneet

rocky

alkesh

];

Names1:

Mapping Load Name,

  RowNo() as No

  Resident List2;

  Drop table List2;

List1:

LOAD *,

  ApplyMap('Names1', Name, 'No') as Number,

  IF(ApplyMap('Names1', Name, 0)=0,'No', 'Yes') as Match;

load * inline [

Name

navneet

balraj

nikita

alkesh

rocky

subodh

];

This way you only have the relevant data in your tables.

Other nice thing about this is that you can use a selection field (Match) where you can select all te matched an non matched names

[#9]

PFA the solution. navneet kaushal