Loading from QVD - where condition based on a resident table

[#1]

I would like to load a specific list of accounts and their orders as shown below. Is it possible?

[Attendee]:

Load ACCOUNT_ID , NAME from ATTENDEE_TAB;  /* Database table */

[ORDER_HEAD]:

load ACCOUNT_ID, ORDER_ID , ORDER_TYPE from ORD_HEAD.qvd  (qvd) /* where condition to limit ACCOUNT_ID to Attendee.ACCOUNT_ID above*/;

[LINE_ITEMS]:

load ORDER_ID, PROD, PRICE, QTY from ORD_LINE_ITEMS.qvd  (qvd) /* where  limited ORDER_ID to ORDER_HEAD.ORDER_ID table*/

My qvds contains data from many years and so many accounts.

In this particular application I wanted to load only data pertaining to a specific set of accounts .

Thanks for your support!

Aji Paul.

[#2]

Forgot to mention the Order header account field name is ORD_ACCOUNT_ID   not just ACCOUNT_ID

Also the ORDER_ID on line item is called  PARENT_ORDER_ID.

[swuehl · Accepted solution]

try something like

[Attendee]:

Load ACCOUNT_ID ,

          NAME

from ATTENDEE_TAB;  /* Database table */

[ORDER_HEAD]:

load ACCOUNT_ID,

       ORDER_ID ,

       ORDER_TYPE

from ORD_HEAD.qvd  (qvd)  

WHERE EXISTS(ACCOUNT_ID);

/* where condition to limit ACCOUNT_ID to Attendee.ACCOUNT_ID above*/

[LINE_ITEMS]:

load ORDER_ID,

         PROD,

         PRICE,

        QTY

from ORD_LINE_ITEMS.qvd  (qvd)

WHERE EXISTS (ORDER_ID);

/* where  limited ORDER_ID to ORDER_HEAD.ORDER_ID table*/

edit: If your field names differ in the tables you want to limit, use EXISTS() with its two argument version EXISTS(FIELD1, FIELD2) to check your input table field FIELD2 value on existence in FIELD1.

[#4]

Thanks

My challenge is the fields are not named the same. Please see my second post.

[anandathome]

You can use this example which does it by left join

Final:

LOAD ACCOUNT_ID,

     NAME

FROM

[.......\PO_File.xls]

(biff, embedded labels, table is [ATTENDEE_TAB$]);

left join

LOAD ACCOUNT_ID,

     ORDER_ID,

     ORDER_TYPE

FROM

[........\PO_File.xls]

(biff, embedded labels, table is [ORD_HEAD$]);

left join

LOAD ORDER_ID,

     PROD,

     PRICE,

     QTY

FROM

[..........\PO_File.xls]

(biff, embedded labels, table is [ORD_LINE_ITEMS$]);

Attached is the sample data.

and result comes as in image

[anandathome]

You can rename the key columns to be of same name so that the join is implicit.

[#7]

Thank You! That worked..