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

Inner Join Not Working

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Inner Join Not Working

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?

11 Replies
MVP
MVP

Re: Inner Join Not Working

you're using an inner join

if there isn't any common values

you get 0 records

example

load * inline [

field

a

b

c

];

inner join

load * inline [

field

aa

bb

cc

];

Not applicable

Re: Inner Join Not Working

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.

MVP
MVP

Re: Inner Join Not Working

yes AccountId is the join field

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

Not applicable

Re: Inner Join Not Working

Yes there definitely are.

MVP
MVP

Re: Inner Join Not Working

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

Join

instead of

Inner Join

jsaradhi
Valued Contributor

Re: Inner Join Not Working

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.

NickHoff
Valued Contributor

Re: Inner Join Not Working

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.

Not applicable

Re: Inner Join Not Working

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.


NickHoff
Valued Contributor

Re: Inner Join Not Working

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.

Community Browser