topic Re: Outer Join in QlikView

Outer Join

Wed, 16 Sep 2015 08:26:39 GMT

Hello everybody,

I’m quite new to QlikView and while programming a dashboard the following question came up:

I have two tables: BSTK and ABK. Both have two colums: BSTK.No, BSTK.Total and ABK.No, ABK.Total.

BSTK ist the order and ABK the confirmation of order. Not every order has a confirmation (see empty fields) and not every order has a value (=0).

I want to make the following inquiry (maybe with an outer join):

If BSTK.Total is empty, then show the value of ABK.Total

If BSTK.Total is not empty and ABK.Total is not empty, then show the value of ABK.Total

If BSTK.Total is not empty and ABK.Total is empty, then show the value of BSTK.Total

If both are empty, then show 0

The results should be shown in a separate Column.

Does anyone have any idea how to solve this? Any help would be much appreciated. Thanks in advance.

Alicelqv

BSTK.No	BSTK.Total	ABK.No	ABK.Total
A00168	40
A00432	30
A00713	10	A00713	8
A00249	0	A00249	15

Re: Outer Join

manojkulkarni — Wed, 16 Sep 2015 08:43:55 GMT

Why do you need join table with outer condition ? With data provided, we can easily handle it in expression itself

Re: Outer Join

MK_QSL — Wed, 16 Sep 2015 08:46:43 GMT

Temp_BSTK:
Load
  *,
  [BSTK.No] as Key
Inline
[
  BSTK.No, BSTK.Total
  A00168, 40
  A00432, 30
  A00713, 10
  A00249, 0
];


Join


ABK:
Load
  *,
  [ABK.No] as Key
Inline
[
  ABK.No, ABK.Total
  A00713, 8
  A00249, 15
];


Left Join (Temp_BSTK)
Load
  Key,
  IF(SUM(ABK.Total) <> 0, SUM(ABK.Total),
  IF(SUM(BSTK.Total) <> 0 and SUM(ABK.Total) = 0, SUM(BSTK.Total),
  IF(SUM(BSTK.Total) = 0 and SUM(ABK.Total) = 0, 0))) as NewTotal
Resident Temp_BSTK
Group By Key;

Re: Outer Join

sasiparupudi1 — Wed, 16 Sep 2015 08:47:30 GMT

I am assuming that you would want to join the tables in qv and then perform the expressions in the script?

please provide sample data for the both tables

Re: Outer Join

qlikviewwizard — Wed, 16 Sep 2015 08:52:58 GMT

Hi,

Please provide the sample data for two tables and expected results. So that it would be easy to provide the solution from QV experts.

Re: Outer Join

nagarjuna_kotha — Wed, 16 Sep 2015 08:53:10 GMT

Is it compulsory to use Outer Join ? and we can also do other way too

Re: Outer Join

qlikmsg4u — Wed, 16 Sep 2015 08:54:49 GMT

Is this what you are looking for?

BSTK:

Load * Inline [

BSTK.No,BSTK.Total

A00168, 40

A00432, 30

A00713, 10

A00249,

];

Left Join(BSTK)

ABK:

Load ABK.No as BSTK.No,ABK.Total;

Load * Inline [

ABK.No, ABK.Total

A00713, 8

A00249, 15

];

NoConcatenate

LOad BSTK.No,ABK.Total,BSTK.Total,If(IsNull(ABK.Total),BSTK.Total,ABK.Total) as New_Column Resident BSTK;

Drop Table BSTK;

Re: Outer Join

Wed, 16 Sep 2015 09:40:32 GMT

Yes, that is exactly the logic I was looking for. Thank you!

But how can I make this with variables instead of the fixed numbers (A00713,A00249,...)? Maybe my question was not precise enough, but the list with the data goes on and I want QlikView to always calculate the new column. E.g. when new orders A00534, A00535,... are made and new confirmations come in, I want the programm to automatically update the chart.

Maybe you can help me with that, too?

Re: Outer Join

sasiparupudi1 — Wed, 16 Sep 2015 09:59:32 GMT

What is your data source? if it is SQL, replace the inline loads with SQL

qualify No Total;

BSTK:

SQL select No,Total from BSTK;

Left Join(BSTK)

ABK:

sql select No,Total from ABK;

final:

Load *,If(IsNull(ABK.Total),BSTK.Total,ABK.Total) as New_Column Resident BSTK;

hth

Sasi

Re: Outer Join

Wed, 16 Sep 2015 12:40:00 GMT

The data source is a data base from our ERP system.

Your version sounds good and logical, but I still don't think that it's the solution (maybe close to it).

Do you have any idea how to solve this with a usual database?