Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Why do you need join table with outer condition ? With data provided, we can easily handle it in expression itself
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;
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
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.
Is it compulsory to use Outer Join ? and we can also do other way too
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;
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?
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
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?