topic Re: compare two columns in QlikView

compare two columns

Wed, 01 Oct 2014 08:48:01 GMT

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

Re: compare two columns

PrashantSangle — Wed, 01 Oct 2014 08:52:03 GMT

Hi,

Rename column

Country1 as Country

and

Country2 as Country

Or you can use Exist()

Regards

Re: compare two columns

alexandros17 — Wed, 01 Oct 2014 09:06:26 GMT

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

Re: compare two columns

martyn_birzys — Wed, 01 Oct 2014 09:13:01 GMT

Consider using ApplyMap()

Re: Re: compare two columns

JonnyPoole — Thu, 02 Oct 2014 03:56:27 GMT

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);

Re: compare two columns

Thu, 02 Oct 2014 04:00:15 GMT

Tanks Jonathan,

what if we have more columns in the two tables?

Re: compare two columns

Anonymous — Thu, 02 Oct 2014 15:16:58 GMT

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.

Re: compare two columns

Fri, 03 Oct 2014 03:37:33 GMT

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

Re: compare two columns

Anonymous — Fri, 03 Oct 2014 15:53:42 GMT

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.

Re: compare two columns

Fri, 03 Oct 2014 16:22:34 GMT

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

Re: compare two columns

Anonymous — Fri, 03 Oct 2014 21:36:31 GMT

there is no way around it.

a computer can not (correctly) interpret data without a set of defining logic that provides context/instructions/rules for interpretation.

for example, it isn't feasible to suggest that there is a way to identify programmatically that the values "East Ind","West Ind", and "Ind" are all equivalent to the correlated field value "INDIA" without the use of a mapping/attribute table which defines the relationship.