Load, merge two source files and create a new field in the merged table

[Anonymous]

I'm pretty new to the Qlikview desktop product and have a need to write a script to create a QVD that will

1. Merge one database file and one Excel file based on a common key "AccountNumber"

2. Create a new column "CalcAmount" in the merged table

if "PaidAmount" <> 0

set CalcAmount = PaidAmount

else

set Calc Amount = SalesAmount

Two source files and the columns in each table are:

1) Table_Sales from a database

* AccountNumber

* SalesAmount

2) Table_Paid from an Excel file

* AccountNumber

* PaidAmount

Examples:

1) Table_Sales

AccountNumber SalesAmount

A 1000

B 1000

C 500

D 700

2) Table_Paid

AccountNumber PaidAmount

A 0

B 999

C -100

3) Merged_Table

AccountNumber SalesAmount PaidAmount CalcAmount

A 1000 0 1000

B 1000 999 999

C 500 -100 -100

D 700 0 700

Any help on the best approach to solve this problem will be greatly appreciated. Thanks

[johnw · Accepted solution]

Something along these lines:

Data:
LOAD
AccountNumber
,SalesAmount
FROM your database
;
OUTER JOIN (Data) // Or left join if sale must exist
LOAD
AccountNumber
,PaidAmount
FROM your Excel file
;
LEFT JOIN (Data)
LOAD *
,if(PaidAmount,PaidAmount,SalesAmount) as CalcAmount
RESIDENT Data
;

STORE Data INTO Data.qvd (QVD);

[Anonymous]

It works like a champ! Thank you John.