Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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?
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
This is what i tried and it worked
But thanks for reaffirming it
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!