Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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
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?
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?
Hi Stefan, it is exactly what i was looking for.
Thanks again!
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
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;
Thanks Stefan