compare two columns

[#1]

Hi,

i have 2 tables and i need the compare the country2 with the Country2 and if both are equal it should get corresponding Sub_Country

Country1:

Sub_Country	Coutnry1
US01	US
Ind01	Ind
Uk01	UK
Ind02	East Ind
Ind03	West Ind

Country2:

Country2
USA
INDIA
UK
INDIA

Note: there is no common column exists

[PrashantSangle]

Hi,

Rename column

Country1 as Country

and

Country2 as Country

Or you can use Exist()

Regards

[alexandros17]

You cannot do this unless there is a mapping column defined, something that associate INDIA to Ind and what about west Ind? How can it be associated to INDIA?

Define a rule and a mapping table

[martyn_birzys]

Consider using ApplyMap()

[JonnyPoole]

Here is a mapping load example

CountryMap:

Mapping load *;

LOAD * INLINE [

    Country, Country2

    US,USA

    Ind,INDIA

];

Countries:

LOAD Sub_Country,

     Coutnry1,

     ApplyMap('CountryMap',Coutnry1,Coutnry1) as MappedCountry

FROM

[http://community.qlik.com/thread/135963]

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

[#6]

Tanks Jonathan,

what if we have more columns in the two tables?

[Anonymous]

Like Jonathan said, you need to use the mapping load in order to correlate the country 2 column into the first table.

mapCountry:

mapping load

     country1,

     country2

from <source2>;

table1:

load

     sub-country,

     applymap('mapCountry',country1) as country2

from <source1>;

At this point, you would have a resulting set of one table containing two fields - sub-country and country2.

Obviously, any additional fields from table 1 can simply be added to the load script.

If you needed to add more fields that originate from table2 which was initially used for the mapping function, then you could load that table again using a left join (or left keep) to the first table on the new field.

ex:

mapCountry:

mapping load

     country1,

     country2

from <source2>;

table1:

load

     sub-country,

     applymap('mapCountry',country1) as country2

from <source1>;

left join (table1)

load

     country2,

     field3,

     field4

from <source2>;

hope that helps.

[#8]

Hi,

Thaks for the reply

mapCountry:

mapping load

     country1,

     country2

from <source2>;

in this mapping load the country2 is from Source2 and Country1 is from Source1

i am gettng error while reloading the script that coutnry1 field doesnot exist because it is in the second table

can you share the app so that i can view it

[Anonymous]

the correlation must be created between country1 and country2...i.e. via an inline table or in an external file such as excel.

then you use that source table ( which holds the correlation ) as the source in your mapping load.

otherwise, there is no way for you to connect the two data elements.

[#10]

Co-relation can be made via external source for only in case of small data or less number of columns

i cant create if there are more than 5 and each column having more than 1000 records

So i am facing the criticality in making the correlation.

Can you kindly let me know with the sample