Two Tables Join

[#1]

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?

[MayilVahanan]

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..

[edg_baltazar]

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!

[goldnejea8]

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

[#5]

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.

[nagaiank]

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

[#7]

Ok will try both the methods.

[#8]

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

[#9]

Input file and result screenshots attached.

TableA, Table B and Table C in sequence.

Result

[#10]

Hi Andrepeter,

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

Hope this helps.

Regards,

Snehal Bhosle