More than one resident table in load statement

[#1]

Is it possible to reference fields from multiple tables in the load script? Here is what I'm trying to do:

• Calendar.qvd
  Fields: Date, Year, Month
• Account.qvd
  Fields: payments, charges

How do I write a statement such that:

if(Date>YYYY-MM-DD, payments*2, payments) as payments

[eduardo_sommer]

No, it is not possible. Besides this, the two tables in your example seems to have no relation to each other. You need to have some way to relate one table to another in order to link them.

Eduardo

[#3]

Is there some kind of relationship at all that could be reflected in your data between the accounts and your calendar? 

[#4]

Yes, there is a common field Account_Number for both QVD's. How do I use it within the load script to relate the 2 tables for that expression?

[#5]

What you can do is a outer join to create only one table. And then you can create your field

[eduardo_sommer]

Hi

Calendar:

Load AccountNumber,

        Date,

        Year,

        Month

From Calendar.qvd (qvd);

outer join (Calendar)

Load AccounNumber,

        payments,

        charges

From Account.qvd (qvd);

Now you have one table with all fields, with nulls in the fields from the nom-matching tables.

After this, you can use a load ... resident Calendar, with your expression.

Eduardo

[#7 · Accepted solution]

As Eduardo said,

Calendar:

Load Account_Number,

        Date,

        Year,

        Month

From Calendar.qvd (qvd);

Accounts:

outer join (Calendar)

Load Account_Number,

        payments,

        charges

From Account.qvd (qvd);

FinalTable:

Load *,

if(Date>YYYY-MM-DD, payments*2, payments) as new_payments

resident Calendar;

drop table calendar;

Hope this helps.

[#8]

Thanks, everybody! That was really helpful!