Question about this table join

[#1]

My qlikview document has a main table being imported from a database and it is called 'loans'. I am also importing a few fields from another database - and these fields reside in several different tables within that database. I am joining these smaller tables to the main loans table by including the account number field and the production date field in all of the tables... If I concatenate all of the smaller tables there will be more than one row for each account number/production date combination in that table. Will this join correctly to the loans table or will there be duplicate records? An example is below:

LOANS:

Account #	Date	Field1	Field2
555	12/31	1	a
444	12/31	2	b
333	12/31	1	c

SMALLER TABLE (after concatenation)

Account #	Date	Field3	Field4	Field5
555	12/31	a
555	12/31		b
555	12/31			c

I would like the resulting row to for account '555' to look like this:

Account #	Date	Field1	Field2	Field3	Field4	Field5
555	12/31	1	a	a	b	c

Thank you!

[anbu1984]

You will have duplicates if you join concatenated table directly to Loans table. Try like this

Loans:

Select * From Loans;

Join(Loans)

Load Account#, Date, Max(Field1), Max(Field1), Max(Field1) Resident Concatenated_Table Group by Account#, Date;

Drop Table Concatenated_Table ;

[#3]

The 'max' function will pick the highest one for the rows with the same account number/ date? Will this work for non-numeric fields?

[Anonymous]

Hi,

What you need as result is a join not a concatenate.

Your smaller tables have to be joined instead of concatenate in order to get this:

Account #	Date	Field3	Field4	Field5
555	12/31	a	b	c

then you can join this table with your LOANS table and you'll get your expected final table.

you can also modify your SMALLER_TABLE like this to get the table to join to LOANS:

SMALLER_TABLE_2:

LOAD

Account #,

Date,

Field3

RESIDENT

SMALLER_TABLE;

JOIN

(SMALLER_TABLE_2)

LOAD

Account #,

Date,

Field4

RESIDENT

SMALLER_TABLE;

JOIN

(SMALLER_TABLE_2)

LOAD

Account #,

Date,

Field5

RESIDENT

SMALLER_TABLE;

DROP TABLE SMALLER_TABLE;

Then:

JOIN

(LOANS)

LOAD

*

RESIDENT

SMALLER_TABLE_2;

DROP TABLE SMALLER_TABLE_2;

[#5]

The reason I used concatenate instead of a join is because some of the smaller tables contain the same field names (other than the original two) and i don't want them to join on anything but account number and production date. Any Idea on how to get around this besides naming the fields different things?

[maxgro]

maxstring

[Anonymous]

I'm not sure to have understood what you explained but I think if you keep the second choice :

SMALLER_TABLE_2:

LOAD

Account #,

Date,

Field3

RESIDENT

SMALLER_TABLE;

JOIN

(SMALLER_TABLE_2)

LOAD

Account #,

Date,

Field4

RESIDENT

SMALLER_TABLE;

JOIN

(SMALLER_TABLE_2)

LOAD

Account #,

Date,

Field5

RESIDENT

SMALLER_TABLE;

DROP TABLE SMALLER_TABLE;

Then:

JOIN

(LOANS)

LOAD

*

RESIDENT

SMALLER_TABLE_2;

DROP TABLE SMALLER_TABLE_2;

It should work fine.