topic joining 2 tables - inner join? in QlikView

joining 2 tables - inner join?

Tue, 29 Mar 2011 14:08:34 GMT

hi, i have 2 tables TABLE A + FINANCIAL DATES.

I want to create a join so that i can include all 'Table A' fields and only selected fields from table 'FINANCIAL DATES'. Where TABLE A.Final issue date = Financial Dates.Final issue date.

My script is below

---------------

[TABLE A]:
LOAD
issue_date ,
date(MonthStart([issue_date]), 'MMM-YY') as [Final issue date],
issue_value
FROM TableA.qvd;

[Financial Dates]:
LOAD Date as [Final issue date],
Month,
[Month Year],
Quarter,
Year
FROM [Financial Qtr and Years .xls];

-------------------------

any ideas how i can amend my script above to achieve this?

joining 2 tables - inner join?

Tue, 29 Mar 2011 14:11:50 GMT

If you are looking for a join of Table1 and only associated values in Table2, then you are looking for a left join(Table1) in front of the load statement of Table2.

Inner join is going to result in creating a table that only contains intersecting values of Table1 & Table2. If that's what you're looking for, then...

joining 2 tables - inner join?

jonathandienst — Tue, 29 Mar 2011 14:14:19 GMT

This should do the trick:

[TABLE A]:
LOAD
issue_date ,
date(MonthStart([issue_date]), 'MMM-YY') as [Final issue date],
issue_value
FROM TableA.qvd;

LEFT Join ([TABLE A])
LOAD Date as [Final issue date],
 Month,
 [Month Year],
 Quarter,
 Year
FROM [Financial Qtr and Years .xls];

Jonathan

joining 2 tables - inner join?

Miguel_Angel_Baeyens — Tue, 29 Mar 2011 14:15:04 GMT

Hello,

I'd use Exists(), although Joining should work as well:

[TABLE A]:LOADissue_date ,date(MonthStart([issue_date]), 'MMM-YY') as [Final issue date],issue_valueFROM TableA.qvd (qvd); [Financial Dates]:LOAD Date as [Final issue date], Month, [Month Year], Quarter, YearFROM [Financial Qtr and Years .xls] WHERE EXISTS([Final issue date], Date);

Hope this helps.