topic Re: More than one resident table in load statement in QlikView

More than one resident table in load statement

Tue, 10 Jun 2014 19:22:52 GMT

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

Re: More than one resident table in load statement

eduardo_sommer — Tue, 10 Jun 2014 19:32:30 GMT

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

Re: More than one resident table in load statement

Tue, 10 Jun 2014 19:33:50 GMT

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

Re: More than one resident table in load statement

Tue, 10 Jun 2014 19:38:15 GMT

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?

Re: More than one resident table in load statement

Tue, 10 Jun 2014 19:40:58 GMT

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

Re: More than one resident table in load statement

eduardo_sommer — Tue, 10 Jun 2014 19:48:49 GMT

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

Re: More than one resident table in load statement

Tue, 10 Jun 2014 20:12:34 GMT

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.

Re: More than one resident table in load statement

Tue, 10 Jun 2014 21:34:41 GMT

Thanks, everybody! That was really helpful!