Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Know match and does not match

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?:

AllCitiesInCitiesNewInCities
New YorkNN
Los AngelesYN
ChicagoNN
HoustonYN
PhiladelphiaNN
NYNY
Nueva YorkNY
PhoenixNY
DallasNY

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

Thanks!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Know match and does not match

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?

7 Replies
MVP
MVP

Re: Know match and does not match

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

Not applicable

Re: Know match and does not match

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?

MVP
MVP

Re: Know match and does not match

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?

Not applicable

Re: Know match and does not match

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

Thanks again!

Not applicable

Re: Know match and does not match

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

MVP
MVP

Re: Know match and does not match

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;

Not applicable

Re: Know match and does not match

Thanks Stefan

Community Browser