topic Re: FULL JOIN equivalent with load? in QlikView

FULL JOIN equivalent with load?

Wed, 14 May 2014 21:18:01 GMT

Hi, all.

I have two tables that appear as follows, counting attempted/successful events in my application:

Attempt Table

key	value	count
1	Authentication	2
1	Account Lookup	1
1	Bill Pay	1

Success Table

key	value	count
1	Authentication	1
1	Account Lookup	1

I am trying to load this data so it appears as follows:

key	event	attempted	successful	delta
1	Authentication	2	1	-1
1	Account Lookup	1	1	0
1	Bill Pay	1	0	0

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?

Thanks!

Eric

Re: FULL JOIN equivalent with load?

Wed, 14 May 2014 21:27:30 GMT

Attached with answer. Please do a JOIN

Attempt:

LOAD key,

value,

count as Attempted

FROM

[http://community.qlik.com/thread/118478]

(html, codepage is 1252, embedded labels, table is @1);

Join

Success:

LOAD key,

value,

count as Success

FROM

[http://community.qlik.com/thread/118478]

(html, codepage is 1252, embedded labels, table is @2)

Where Len(Trim(key))>0;

Final:

Load *, if(isnull(Success),0,Success-Attempted) as Delta

Resident Attempt;

Drop table Attempt;

Re: Re: FULL JOIN equivalent with load?

Wed, 14 May 2014 21:37:53 GMT

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).

Attempt table:

key	value	count

Success table:

key	value	count
2	Authentication	1

Result table:

key	event	attempted	successful	delta
2	Authentication	0	1	1

What's the equivalent, here?

Thanks,

Eric

Re: FULL JOIN equivalent with load?

Wed, 14 May 2014 21:49:19 GMT

The code I mentioned will be the same but a slight change is required here

Final:

Load *,

if(isnull(Success),0,Success-Attempted) as Delta

Load key,

value,

if(isnull(Attempted,0,Attempted) as Attempted,

Success

Resident Attempt;

The JOIN command is similar to FULL OUTER JOIN in sql

Re: FULL JOIN equivalent with load?

Colin-Albert — Wed, 14 May 2014 21:50:50 GMT

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!

Attempt:

LOAD key,

value,

count as Attempted

FROM <attempt data> ;

concatenate(Attempt)

LOAD key,

value,

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.