Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

FULL JOIN equivalent with load?

Hi, all.

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

Attempt Table

keyvaluecount
1Authentication2
1Account Lookup1
1Bill Pay1

Success Table

keyvaluecount
1Authentication1
1Account Lookup1

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

keyeventattemptedsuccessfuldelta
1Authentication21-1
1Account Lookup110
1Bill Pay100

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: FULL JOIN equivalent with load?

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

4 Replies
Not applicable

Re: FULL JOIN equivalent with load?

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;

Not applicable

Re: Re: FULL JOIN equivalent with load?

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:

keyvaluecount

Success table:

keyvaluecount
2Authentication1

Result table:

keyeventattemptedsuccessfuldelta
2Authentication011

What's the equivalent, here?

Thanks,

Eric

Not applicable

Re: FULL JOIN equivalent with load?

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?

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.

Community Browser