Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

More than one resident table in load statement

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

1 Solution

Accepted Solutions
Not applicable

Re: More than one resident table in load statement

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.

7 Replies
eduardo_sommer
Valued Contributor

Re: More than one resident table in load statement

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

Not applicable

Re: More than one resident table in load statement

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

Not applicable

Re: More than one resident table in load statement

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?

Not applicable

Re: More than one resident table in load statement

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

eduardo_sommer
Valued Contributor

Re: More than one resident table in load statement

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

Not applicable

Re: More than one resident table in load statement

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.

Not applicable

Re: More than one resident table in load statement

Thanks, everybody! That was really helpful!