Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Outer Join

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.NoBSTK.TotalABK.NoABK.Total
A0016840
A0043230
A0071310A007138
A002490A0024915
9 Replies
manojkulkarni
Valued Contributor II

Re: Outer Join

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

Re: Outer Join

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;

sasiparupudi1
Honored Contributor III

Re: Outer Join

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

Arjunarao
Honored Contributor II

Re: Outer Join

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.

nagarjuna_kotha
Valued Contributor II

Re: Outer Join

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

qlikmsg4u
Valued Contributor

Re: Outer Join

Is this what you are looking for?

Capture.PNG

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;

Not applicable

Re: Outer Join

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?

sasiparupudi1
Honored Contributor III

Re: Outer Join

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

Not applicable

Re: Outer Join

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?

Community Browser