topic Re: Question about this table join in QlikView

Question about this table join

Fri, 27 Feb 2015 16:04:13 GMT

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!

Re: Question about this table join

anbu1984 — Fri, 27 Feb 2015 16:12:18 GMT

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 ;

Re: Question about this table join

Fri, 27 Feb 2015 16:19:27 GMT

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

Re: Question about this table join

Anonymous — Fri, 27 Feb 2015 16:19:54 GMT

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;

Re: Question about this table join

Fri, 27 Feb 2015 16:23:41 GMT

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?

Re: Question about this table join

maxgro — Fri, 27 Feb 2015 16:25:49 GMT

maxstring

Re: Question about this table join

Anonymous — Fri, 27 Feb 2015 16:27:36 GMT

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.