Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Question about this table join

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 #DateField1Field2
55512/311a
44412/312b
33312/311c

SMALLER TABLE (after concatenation)

Account #DateField3Field4Field5
55512/31a
55512/31b
55512/31c

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

Account #DateField1Field2
Field3Field4Field5
55512/311aabc

Thank you!

6 Replies
anbu1984
Not applicable

Re: Question about this table join

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 ;

Not applicable

Re: Question about this table join

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

sbobbyraj
Not applicable

Re: Question about this table join

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 #DateField3Field4Field5
55512/31abc

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;

Not applicable

Re: Question about this table join

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
Not applicable

Re: Question about this table join

maxstring

sbobbyraj
Not applicable

Re: Question about this table join

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.