topic Re: Matching Two Coloumns in QlikView

Matching Two Coloumns

Wed, 30 Apr 2014 07:54:30 GMT

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

Re: Matching Two Coloumns

anbu1984 — Wed, 30 Apr 2014 09:57:44 GMT

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;

Re: Matching Two Coloumns

jagan — Wed, 30 Apr 2014 10:26:58 GMT

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.

Re: Matching Two Coloumns

SunilChauhan — Wed, 30 Apr 2014 10:34:09 GMT

see the attched file

hope this helps

Re: Matching Two Coloumns

its_anandrjs — Wed, 30 Apr 2014 11:00:51 GMT

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

Re: Matching Two Coloumns

its_anandrjs — Wed, 30 Apr 2014 12:18:38 GMT

Hi,

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

Regards

Re: Matching Two Coloumns

maxgro — Wed, 30 Apr 2014 21:14:13 GMT

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;

Re: Matching Two Coloumns

Anonymous — Fri, 02 May 2014 14:59:26 GMT

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

Re: Matching Two Coloumns

Fri, 02 May 2014 15:10:30 GMT

PFA the solution. navneet kaushal