Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner
Partner

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

MK_QSL
MVP
MVP

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
Master III
Master III

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

qlikviewwizard
Master II
Master II

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
Partner
Partner

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

qlikmsg4u
Specialist
Specialist

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
Author

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
Master III
Master III

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
Author

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?