Discussion Board for collaboration on QlikView Scripting.
I have two tables that appear as follows, counting attempted/successful events in my application:
I am trying to load this data so it appears as follows:
In SQL, I'd do this with a FULL JOIN on key and value from both tables, and converting null counts to to zeroes.
How do I accomplish the same using QV's LOAD mechanism?
Go to Solution.
The code I mentioned will be the same but a slight change is required here
if(isnull(Success),0,Success-Attempted) as Delta
if(isnull(Attempted,0,Attempted) as Attempted,
The JOIN command is similar to FULL OUTER JOIN in sql
Attached with answer. Please do a JOIN
count as Attempted
(html, codepage is 1252, embedded labels, table is @1);
count as Success
(html, codepage is 1252, embedded labels, table is @2)
Load *, if(isnull(Success),0,Success-Attempted) as Delta
Drop table Attempt;
Hi, Ajay. Thanks for the quick response!
There is an edge case I have to account for, where the application misbehaves and produces a Success event without the corresponding Attempt (hence, the FULL JOIN instead of a conventional LEFT JOIN).
What's the equivalent, here?
Why not simply concatenate the data and then sum in the chart to get the result.
No need for a join - this is QlikView not a database!
FROM <attempt data> ;
count as Successful
FROM <successful data> ;
You chart then has key & value as dimensions and sums Attenpted and Successful.
You will need to replace the FROM lines with valid expressions for your data.