topic Re: Know match and does not match in QlikView

Know match and does not match

Sun, 21 Aug 2011 20:08:40 GMT

Hi,

If I have the following 2 list:

CitiesList
New York
Los Angeles
Chicago
Houston
Philadelphia

Cities
NY
Los Angeles
Nueva York
Phoenix
Dallas
Houston

How to get something like this?:

AllCities	InCities	NewInCities
New York	N	N
Los Angeles	Y	N
Chicago	N	N
Houston	Y	N
Philadelphia	N	N
NY	N	Y
Nueva York	N	Y
Phoenix	N	Y
Dallas	N	Y

I need to know the cities that matches in 2 lists, and the differences between them.

Thanks!

Re: Know match and does not match

swuehl — Sun, 21 Aug 2011 20:40:14 GMT

Hi thefourth,

hope I got your issue correctly. Please see attached example. I haven't used match function, but simple counts after building a common concatenated table.

(I don't really need the links to the other tables, we can get rid of the links by simple renaming the table3 fields and expression in straight table, if needed.

Regards,

Stefan

Re: Know match and does not match

Sun, 21 Aug 2011 20:54:29 GMT

Hi Stefan, thanks for your help.

Do you know if its possible to get the InCities and NewInCities as dimensions to select and filter the data?

Re: Know match and does not match

swuehl — Sun, 21 Aug 2011 21:04:03 GMT

Sure, you could use similar expressions as in the straight table to create new fields in the script.

See attached sample.

Is this like you want it?

Re: Know match and does not match

Mon, 22 Aug 2011 05:03:20 GMT

Hi Stefan, it is exactly what i was looking for.

Thanks again!

Re: Know match and does not match

Tue, 24 Sep 2013 21:57:52 GMT

Hi Stefan -

I have a PE for Qlikview can you Explain your approach in a doc file as I have a similar issue.

Thanks in Advance -

Syed

Re: Know match and does not match

swuehl — Wed, 25 Sep 2013 10:32:46 GMT

This is the script code:

table1:

LOAD CitiesList

FROM

[http://community.qlik.com/thread/33900?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD Cities

FROM

[http://community.qlik.com/thread/33900?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

table3:

LOAD CitiesList as AllCities,CitiesList

resident table1;

Concatenate Load Cities as AllCities, Cities

resident table2;

table4:

load AllCities,

if(count(Cities) and count(CitiesList),'Y','N') as InCities,

if(count(CitiesList)=0 and count(Cities),'Y','N') as NewInCities

resident table3 group by AllCities;

Re: Know match and does not match

Thu, 26 Sep 2013 20:14:39 GMT

Thanks Stefan