topic Re: Resident table inner join issue in QlikView

Resident table inner join issue

Tue, 29 Oct 2013 08:11:24 GMT

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

Re: Resident table inner join issue

rajni_batra — Tue, 29 Oct 2013 09:25:43 GMT

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;

Re: Resident table inner join issue

Tue, 29 Oct 2013 15:02:03 GMT

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?

Re: Resident table inner join issue

jonathandienst — Tue, 29 Oct 2013 15:20:15 GMT

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

Re: Resident table inner join issue

Tue, 29 Oct 2013 15:23:28 GMT

This is what i tried and it worked

But thanks for reaffirming it

Re: Resident table inner join issue

Carlos_Reyes — Tue, 29 Oct 2013 15:25:07 GMT

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!