topic Re: Full outer join with QVDs on some condition in QlikView

Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 20:26:48 GMT

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.

Re: Full outer join with QVDs on some condition

oscar_ortiz — Tue, 18 Nov 2014 20:33:49 GMT

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

Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 20:36:22 GMT

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

Re: Full outer join with QVDs on some condition

oscar_ortiz — Tue, 18 Nov 2014 20:42:40 GMT

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.

Re: Full outer join with QVDs on some condition

maxgro — Tue, 18 Nov 2014 20:44:45 GMT

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

Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 20:47:42 GMT

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.

Re: Full outer join with QVDs on some condition

maxgro — Tue, 18 Nov 2014 20:50:05 GMT

rename the field you want to join

load

....

field1 as field2,

.....

Re: Full outer join with QVDs on some condition

oscar_ortiz — Tue, 18 Nov 2014 20:52:54 GMT

Rename the field...

FldX as Fld1,

FldY as Fld2

Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 20:58:08 GMT

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

Re: Full outer join with QVDs on some condition

maxgro — Tue, 18 Nov 2014 21:02:44 GMT

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

...........

;

Re: Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 21:02:53 GMT

Thank you. I have a small question. How can I use an Oracle to_char function in LOAD statement? Could you please help me with that?

Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 21:05:42 GMT

Can I not do it in a load statement? I have all my data in a QVD

Re: Full outer join with QVDs on some condition

maxgro — Tue, 18 Nov 2014 21:11:18 GMT

the to_char is an Oracle function

in the load section you can only use Qlik function and there are are a lot of string function

list is in Qlik online help......

what do you want to do with the to_char function in Qlik?

Re: Full outer join with QVDs on some condition

reshmakala — Tue, 18 Nov 2014 21:13:11 GMT

My field in first table was created as a number and the same filed in another table was of varchar type. So I am finding it difficult to compare them.

Re: Full outer join with QVDs on some condition

maxgro — Tue, 18 Nov 2014 21:19:50 GMT

maybe with

text

num#

from online help

text(expression )

The text function forces the expression to be treated as text, even if a numeric interpretation is possible.