Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
see the attched file
hope this helps
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
Hi,
If you get the correct answer from the thread so mark the thread as select appropriate answer as correct / Helful for references.
Regards
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;
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
PFA the solution. navneet kaushal