Full outer join with QVDs on some condition

[reshmakala]

Hello all,

I have a script like

Load .....;

SELECT * FROM

( Select * from tableA where condition) T1

Full outer join

(Select * from table B) T2

on ((T1.field1=T2.field1) and (T1.field2=T2.field2));

I now have two separate QVDs

1) One for Select * from tableA and other for SELECT * FROM TableB

How can I perform full outer join when loading from QVDs with a where condition for the join??

Thanks in advance.

[oscar_ortiz]

It sounds like you wan to accomplish the same bit of code using QVD's instead of DB tables.

If that's the case you can try something like this.

T1:

Load

     ...

     ...

From QVD1

Where

     YourCondition

;

Outer Join (T1)

Load

     ...

     ...

From QVD2

Where

     YourCondition

;

Good luck

Oscar

[reshmakala]

Hello,

Thanks for your response. Will the script you mentioned be equal to what I have from DB tables?

I need to perform the join on some condiitons.

on ((T1.field1=T2.field1) and (T1.field2=T2.field2)); ----> I want how to script this part with QVDs

[oscar_ortiz]

QlikView by default will join by like fields.  So in your case T1.Fld1 = T2.Fld1 and T1.Fld2 = T2.Fld2 will automatically be taken care of.

The thing to be aware of is how many other fields that might have the same name.  You may not want those conditions to exists, if so you'll need t do some renaming of fields.  If not no worries.

[maxgro]

table:

Load field1, field2, ...... from tableAQvd where .....;

join (table)

Load field1, field2, ..... from tableBQvd where ......:

Qlik will join using fields with same name

Qlik will do (default) an outer join

[reshmakala]

Thanks!! If the condition is T1.FieldA = T2.FieldB..I mean to say if the field names are different? Is there anything else that I need to add or something?

Because My field1 in both the tables do not have a same name.

[maxgro · Accepted solution]

rename the field you want to join

load

....

field1 as field2,

.....

[oscar_ortiz]

Rename the field...

FldX as Fld1,

FldY as Fld2

[reshmakala]

Thank you. I have a small question. How can I use an Oracle to_char function in LOAD statement?

[maxgro]

in the sql section of qlik script (in bold)

when a sql query works in Oracle (sqlplus, toad, etc....) you can usually use it in Qlik

load

     .....

     ;

SQL

     select

          to_char(.............),

          .........

     from

          ...........

     ;