Know match and does not match

[#1]

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!

[swuehl]

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

[#3]

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?

[swuehl · Accepted solution]

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?

[#5]

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

Thanks again!

[#6]

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

[swuehl]

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;

[#8]

Thanks Stefan