topic Re: Two Tables Join in QlikView

Two Tables Join

Fri, 13 Jul 2012 21:22:42 GMT

Hi,

If I have below two tables, how to combine those two ?

Table_A:

Load

Name,

City,

Occupation,

Income

From qvda;

Table_B:

Load

Name,

City,

SSN,

Blue__Red_Ind

From qvdb;

I want output whenver Name and City combination matches from Table A to Table B. (If data for A is present in B populate Blue_red_Ind else put blanks in field Blue_Red_Ind.)

Expected table columns

Name,City,Occupation,Income,City,Blue_Red_Ind

I was thinking Simple Join should work , but not sure if it is Friday Night so its not working.

Should I try to concatenate both fields(Name and City ) to create a new field? Is it possible?

Re: Two Tables Join

MayilVahanan — Fri, 13 Jul 2012 21:32:49 GMT

HI,

Try this,

Without join, itself, qlikview automatically join all fields based on name and city ..

Want to merge in single table, use,

Table_A:

Load

Name,

City,

Occupation,

Income

From qvda;

Table_B:

Concatenate(Table_A)

Load

Name,

City,

SSN,

Blue__Red_Ind

From qvdb;

Table:

Load *,If(Len(Trim(Blue_Red_Ind)) > 0, Blue_Red_Ind) Resident Table_B;

Drop table Table_B;

Hope it helps..

Re: Two Tables Join

edg_baltazar — Fri, 13 Jul 2012 21:35:21 GMT

Yes you are right! , you have to create a new keyfield with by the concatenation with that two fields (Name and City) and make a link_table where you would have the Name and City fields and a KeyField to link the qvda and the qvdb tables!, hope this helps, Good Luck!

Re: Two Tables Join

goldnejea8 — Fri, 13 Jul 2012 21:38:13 GMT

First, why do you want 2 city fields? Do they house different information? i.e. Birth City, and Work City..

Second, I would try a mapping table. This way, you don't have to do a join, yet you get the same result and don't run the risk of getting weird data.

(Untested)

//Create Mapping Table

MapTable:

Mapping Load Distinct

Name&'|'&City as NameCity,

Blue__Red_Ind

From

Table B;

//Apply Map to table A's load

Table_A:

Load

Name,

City,

Occupation,

Income,

ApplyMap('MapTable',Name&'|'&City,Null()) as Blue__Red_Ind

From qvda;

The apply map might not work when you are doing the concatenation in the expression. If not, I would create a temp table, do the concatenation, save it off, then load from that file.

Hope this helps

Re: Two Tables Join

Fri, 13 Jul 2012 21:55:25 GMT

I used Mayil's solution.

But below part didn't work.

Table:

Load *,If(Len(Trim(Blue_Red_Ind)) > 0, Blue_Red_Ind) Resident Table_B;

I tried to change it to

Table_C:

Load *, Resident Table_B;

Drop Table Table_B

=========================================================================

Having said that I got joined view of Table A combined with Table_B Data. ofocurse need to test if this join has extracted correct data from Table_B.

Re: Two Tables Join

nagaiank — Fri, 13 Jul 2012 22:04:29 GMT

Try the following script which will work.

TempA:

load Name & '|' & City as Key, Occupation, Income;

load * from qvda;

TempB:

load Name & '|' & City as Key, SSN, Blue_Red_Ind;

load * from qvdb;

TempAB:

load Key resident TempA;

Concatenate load Key resident TempB;

TableAB:

NoConcatenate

load distinct Key resident TempAB;

Left Join (TableAB) load * resident TempA;

Left Join (TableAB) load * resident TempB;

Drop Tables TempA, TempB;

Table:

load *, SubField(Key,'|',1) as Name, SubField(Key,'|',2) as City;

load * resident TableAB;

Drop Field Key From Table;

Drop Table TableAB;

Message was edited by: Nagaian Krishnamoorthy

Re: Two Tables Join

Fri, 13 Jul 2012 22:59:18 GMT

Ok will try both the methods.

Re: Two Tables Join

Mon, 16 Jul 2012 03:48:44 GMT

I tried something in my personal edition (Qv version 11). to join three tables A,B and C.

My purpose was to extract extra information from Table B and C for Name and City combination mentioned in Table A. i hope this looks ok.

please see attached input files

TableA:
LOAD Name,
     City,
     Occupation,
     Income
FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD
     Name,
     City,
     SSN,
     Blue_Red_Ind,
     Income
FROM

(ooxml, embedded labels, table is Sheet1);

Left Join

Load
City,
State,
Country
From

(ooxml, embedded labels, table is Sheet1);

Re: Two Tables Join

Mon, 16 Jul 2012 03:57:31 GMT

Input file and result screenshots attached.

TableA, Table B and Table C in sequence.

Result

Re: Two Tables Join

Mon, 16 Jul 2012 12:23:33 GMT

Hi Andrepeter,

Please find the attached qvw file wherein i have linked the two tables using linktable..

Hope this helps.

Regards,

Snehal Bhosle

Re: Two Tables Join

Mon, 16 Jul 2012 12:51:09 GMT

Mayil, Snehal, krishnamoorthy

Thanks a lot all of you for providing solutions.

As I mentioned earlier inner join also seems to be working somehow. But good to know these other options incase inner join gives other problems. This was really healpful.

Re: Two Tables Join

Mon, 16 Jul 2012 13:04:24 GMT

Sorry I meant to say "Left Join" seems to be working as of now. But good to know these other options incase left join gives other problems. This was really helpful.