Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agrimroquette
Contributor II

Compare Match

Hi,

I have two columns

Name

Name2

Agrim

agRim

Laxman

laxman

abc

Abc

bac

BAC

dEf

Def

I want to compare Name and Name2

& want output as “Yes” if all value of Name exist in Name2 and

“No” if all value of Name does not exist in Name2

And list of values that does not exist mutually in Name and Name2

Thanks,

Tags (2)
9 Replies
agrimroquette
Contributor II

Re: Compare Match

what I tried is-

MAPPING:

MAPPING LOAD * inline [

Old, New

0, NEGATIVE

1, POSITIVE

];

map [Compare] using MAPPING;

LOAD

    Name,

    Name2,

    wildmatch( Name,Name2) as Compare

FROM [lib://Case/test.xlsx]

(ooxml, embedded labels, table is Sheet1);

but i want automatic comparison and output as Positive or Negative if value don't match in both fields

MVP
MVP

Re: Compare Match

Are you trying to get the output in front-end (may be in a text box)? Is your comparison case sensitive?

agrimroquette
Contributor II

Re: Compare Match

yes its case sensitive and please provide idea to get output in front-end (may be in a text box)

MVP
MVP

Re: Compare Match

Try expression like:

For case INsensitive match (in text box)

='All match : ' & If(not Max(upper(Name)=upper(Name2)), 'NO', 'YES')

Case sensitive

='All match : ' & If(not Max(Name=Name2), 'NO', 'YES')


To get the list of names:


='Not Matched Name : ' & Concat( Distinct If(not Name=Name2,  Name), ',')

='Not Matched Name2 : ' & Concat( Distinct If(not Name=Name2,  Name2), ',')


If you need case insensitive result, just use upper()/lower() like shown above.



agrimroquette
Contributor II

Re: Compare Match

Hi Tresesco,

Thanks for kind help but i have two files and my scripts is as follows

i want to match Assetname and Item and want output that u suggested, where do i put these formula? please help

LOAD

    Assetname,

    Statename

FROM [lib://Test Case/web40reppartofdomain.xls]

(biff, embedded labels, table is report$);

LOAD

    Catégorie,

    Item,

    "Date d'installation"

FROM [lib://Test Case/web40reppartofdomain.xls]

(biff, embedded labels, table is Feuil1$);

laddu_927
Valued Contributor

Re: Compare Match

Is there any relation between two tables that you are loading?? If not, i think we should create one and then replace

Name and Name2 with Assetname and Item .

agrimroquette
Contributor II

Re: Compare Match

no there is no link between these two tables this is problem, how to do it as one?

agrimroquette
Contributor II

Re: Compare Match

but there is a column of dates in both files

agrimroquette
Contributor II

Re: Compare Match

Hi Tresesco,

please help i have two sheets in excel and they have no relation common in between how can i achieve my goal? i have mentioned my script

Regards