topic Re: Outer Join? in QlikView

Outer Join?

Wed, 24 Jun 2015 08:22:21 GMT

Hi,

I am quite new with QV. I have the following situation where the BusinessDates_temp.BusinessDate is not recognised by QV. It used to be like the below but I don't want to hardcode to one date PositionDate, I need a join of all possible Date values from SMI_Collateral. I guess I am trying to make it look like SQL! Any ideas how it should be done?

date($(PositionDate),'DD-MM-YYYY')as %Key_BusinessDate, &'_'& %Key_AccountCode as %Key_BusinessDate_AccountCode

BusinessDates_temp:
load distinct SMI_Collateral.BusinessDate
Resident
SMI_Collateral;

Position_Map_temp:
Outer Join ( BusinessDates_temp)
LOAD
BusinessDates_temp.BusinessDate &'_'& %Key_AccountCode as %Key_BusinessDate_AccountCode,
//Key_BusinessDate_AccountCode ,
%Key_AccountCode,
[SMI_Account-1.AccountExternalCode] as %Key_AccountExternalCode,
SMI_Account.AgreementCode AS %Key_Agreement
RESIDENT
SMI_Account;

Thanks,

Panos.

Re: Outer Join?

sgrice — Wed, 24 Jun 2015 08:58:32 GMT

Qlikview will Outer Join on any fields with the same Name between the two tables.

You do not have any common fields between
BusinessDates_temp

and the Newly loaded

Position_Map_temp

so not sure what you trying to achieve?

If you only what records in Position_Map_temp that have dates in another table

Try

Where Exists(BusinessDate) or where Exists(theFieldtoCheck,theValuetoCheckExists)

Re: Outer Join?

vardhancse — Wed, 24 Jun 2015 09:04:56 GMT

Outer join condition:

Outer

The explicit Join prefix can be preceded by the prefix outer in order to specify an outer join. In an outer join all combinations between the two tables are generated. The resulting table will thus contain combinations of field values from the raw data tables where the linking field values are represented in one or both tables. The outer keyword is optional.

outer join [ (tablename ) ](loadstatement |selectstatement )

Example:

Table1
A	B
1	aa
2	cc
3	ee

Table2
A	C
1	xx
4	yy

select * from table1;

join select * from table2;

select * from table1;

outer join select * from table2;

Joined table
A	B	C
1	aa	xx
2	cc	-
3	ee	-
4	-	yy

Re: Outer Join?

jonathandienst — Wed, 24 Jun 2015 09:40:03 GMT

The names in Qlikview are not the same as SQL. These two fields

[SMI_Collateral.BusinessDate]

[BusinessDates_temp.BusinessDate]

are not related in any way - note the square brackets. These are not qualified names as in SQL, they are just different names. And QV field names are case sensitive, so FieldA and fielda are different fields.

Also this bit of code does not make sense

date($(PositionDate),'DD-MM-YYYY')as %Key_BusinessDate,

&'_'& %Key_AccountCode as %Key_BusinessDate_AccountCode

I think you mean

date($(PositionDate),'DD-MM-YYYY') as %Key_BusinessDate,

date($(PositionDate),'DD-MM-YYYY') &'_'& %Key_AccountCode as %Key_BusinessDate_AccountCode

Re: Outer Join?

Thu, 25 Jun 2015 10:38:50 GMT

OK let me rephrase it then. I have 2 tables A, B and they are linked with column C3. I would like to produce a join between the two tables where one resulting column will be the concatenation of A.Col1 and B.Col2 . Is that possible or do I need to save into a table and get the concatenation from a second separate step. Thanks!