Resident table inner join issue

[#1]

Hi Guys,

I have this following script

[Account]:

LOAD ListID,

     Name,

     FullName,

     IsActive,

     AccountNumber,

     AccountType

FROM

[$(Path)$(_CompFileName)_v_Account.qvd]

(qvd);

NoConcatenate

[SalesReceipt]:

LOAD TxnID,

'Sales Receipt' as TxnType,

CheckNumber,

Date(TxnDate, 'MM/DD/YYYY') as TxnDate,

DepositToAccountListID,

DepositToAccountFullName

FROM

[$(Path)$(_CompFileName)_v_SalesReceipt.qvd]

(qvd);

NoConcatenate

[Temp_Table]:

LOAD

TxnID,

TxnType,

CheckNumber,

TxnDate,

DepositToAccountFullName,

DepositToAccountListID

Resident SalesReceipt;

inner join (Temp_Table)

LOAD

ListID,

FullName,

AccountNumber,

AccountType

Resident Account Where Temp_Table.DepositToAccountListID = ListID and AccountType = 'Bank';

store temp into 'c:\Temp2.qvd';

It gives error "Field Not found Temp_Table.DepositToAccountListID"

Can you guys please help me figure out what is going on here?

Thanks,

Saurabh

[rajni_batra]

ur syntax is wrong

"Where Temp_Table.DepositToAccountListID = ListID"

Try:

[Temp_Table]:

LOAD

TxnID,

TxnType,

CheckNumber,

TxnDate,

DepositToAccountFullName,

DepositToAccountListID

Resident SalesReceipt;

inner join (Temp_Table)

LOAD

ListID,

FullName,

AccountNumber,

AccountType

Resident Account where AccountType = 'Bank';

load

*,Junk

resident Temp_Table where DepositToAccountListID = ListID;

drop table Temp_Table;

[#3]

Hi Batra,

I am not getting syntax error but it is returning more rows.

SalesReceipt has only Account named "Checking" which is Bank type so It should be getting only rows with "Checking" account but it gets me rows all 3 bank accounts which are in Account Table. So total 24 rows are returned.

I am trying other options.

In the mean time can you please tell me rules of join...

can't we keep more than one "where" clause in one load?

[jonathandienst · Accepted solution]

Hi

You can only reference fields in the table being joined/loaded (Account in this case). You should get the load you require like this:

inner join (Temp_Table)

LOAD

ListID As DepositToAccountListID,

ListID,

FullName,

AccountNumber,

AccountType

Resident Account

Where AccountType = 'Bank';

The inner join will removed all records not referenced in the join. If that is nit what you require, use a left join (keep all the  first table fields - Temp_Table) or right join (keep all the second table fields - Account).

HTH

Jonathan

[#5]

This is what i tried and it worked

But thanks for reaffirming it

[Carlos_Reyes]

In QlikView you cannot reference fields in other tables as you'd normally do in a DB query.

Try:

[Temp_Table]:

NoConcatenate

LOAD

TxnID,

TxnType,

CheckNumber,

TxnDate,

DepositToAccountFullName,

DepositToAccountListID

Resident SalesReceipt;

inner join (Temp_Table)

LOAD

ListID     as     DepositToAccountListID,

FullName,

AccountNumber,

AccountType

Resident Account

Where  AccountType = 'Bank';

store Temp_Table into 'c:\Temp2.qvd';

In order yo do a JOIN (ANY KIND) you must make sure that the fields that will serve as links are called the same in both tables, In this case you must rename ListID as DepositToAccountListID so that QlikView knows that those fields values must be equal in order to joind the records.

Hope it helps!