topic Re: Inner Join Not Working in QlikView

Inner Join Not Working

Fri, 27 Feb 2015 18:25:40 GMT

I am tryinig to join these two datsets. The script runs with no errors, getting 90K records from the 1st data set and 100K records from the 2nd data set. However, if I try to create a straight table or table box there are no records showing...just the column/field names. Where's the data?

/*** GET DATA FROM ACCOUNT_BAN_TAXID ***/

Load *,
Account__c as AccountId;

SQL SELECT Account__c,
BAN__c,
Id,
Name,
Tax_ID__c
FROM Account_BAN_Tax_ID__c;

Inner Join

/*** GET DATA FROM ACCOUNT ***/
Load *,
Id as AccountId,
Name as AccountName;

SQL SELECT BANs__c,
Name,
Id,
Tax_id_2__c,
LastActivityDate,
Total_Sims__c
FROM Account;

drop Field Name, Id;

Thanks, Dan

Re: Inner Join Not Working

maxgro — Fri, 27 Feb 2015 18:29:25 GMT

you're using an inner join

if there isn't any common values

you get 0 records

example

load * inline [

field

];

inner join

load * inline [

field

];

Re: Inner Join Not Working

Fri, 27 Feb 2015 18:30:36 GMT

But I thought AccountId would be the link between the two files. When I load them without the inner join, they get linked together automatically by Qlikview on AccountId. I'm trying to join them into one table so I can export as a .txt file.

Re: Inner Join Not Working

maxgro — Fri, 27 Feb 2015 18:32:34 GMT

yes AccountId is the join field

but are there common values of AccountId in the 2 tables?

Re: Inner Join Not Working

Fri, 27 Feb 2015 18:34:23 GMT

Yes there definitely are.

Re: Inner Join Not Working

maxgro — Fri, 27 Feb 2015 18:37:50 GMT

could you post your .qvw after a reload of data with

Join

instead of

Inner Join

Re: Inner Join Not Working

Anonymous — Fri, 27 Feb 2015 18:46:48 GMT

the two accountid fields may have different values - could be the format or the value itself.

before you do the inner join, check your Account__c and Id fields, their formats and values to do a comparison and to make sure you have the same values in the same format in both the fields. remember that Qlikview is case-sensitive.

Re: Inner Join Not Working

NickHoff — Fri, 27 Feb 2015 19:36:35 GMT

Load the data without the join and compare the two fields you are trying to join by in a table box field. Find your differences and correct it.

Re: Inner Join Not Working

Fri, 27 Feb 2015 21:56:33 GMT

There are no differences. The ID is the same in both tables. When loaded without a join statement in the script Qlikview makes the join so I can see them in a chart and the join is done just fine. However, I want the join in the script so I can export the data in one .txt file.

Re: Inner Join Not Working

NickHoff — Fri, 27 Feb 2015 22:24:01 GMT

They are not being inner joined, they are being associated by a common field. The inner join is going to create a new table from the other two tables only where your common fields match. I couldn't imagine the association would work without creating a synthetic key since you have two fields with the same name.

Instead of using a inner join it sounds like you are more interested in concatenating the two tables together. Especially since both tables have the same table structure. In the concatenate you can do a WHERE EXIST(Id,Id). There wouldn't be a need to alias ID or name like you are doing.

Also, when you mention the script runs preview your table in the table viewer. Control T, and then preview the table and see what data is there. If a field is missing from the preview then something is wrong with your formatting.

Re: Inner Join Not Working

swuehl — Fri, 27 Feb 2015 22:37:56 GMT

Dan, when you expand the star symbols, you will get

/*** GET DATA FROM ACCOUNT_BAN_TAXID ***/

Load

Account__c,

BAN__c,

Id,

Name,

Tax_ID__c,

Account__c as AccountId;

SQL SELECT Account__c,
BAN__c,
Id,
Name,
Tax_ID__c
FROM Account_BAN_Tax_ID__c;

Inner Join

/*** GET DATA FROM ACCOUNT ***/
Load

     BANs__c,
    Name,
     Id,
     Tax_id_2__c,
     LastActivityDate,
     Total_Sims__c,
     Id as AccountId,
     Name as AccountName;

SQL SELECT BANs__c,
Name,
Id,
Tax_id_2__c,
LastActivityDate,
Total_Sims__c
FROM Account;

drop Field Name, Id;

So your join is actually joining on three fields, Name, Id, and AccountId, right? Won't work, I believe, but don't know your data.

When you remove the join, the drop field statement will kick in at the end, so you get an association only by AccountId in that case.

Does this sound reasonable?

Re: Inner Join Not Working

Fri, 27 Feb 2015 23:00:57 GMT

This worked, swuhel. By ensuring I was only joining on AccountId the join in the script worked. Thanks to all who took the time to respond...much appreciated!