topic Re: Loading from QVD - where condition based on a resident table in QlikView

Loading from QVD - where condition based on a resident table

Wed, 05 Jun 2013 20:55:26 GMT

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.

Re: Loading from QVD - where condition based on a resident table

Wed, 05 Jun 2013 21:15:12 GMT

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.

Re: Loading from QVD - where condition based on a resident table

swuehl — Wed, 05 Jun 2013 21:15:37 GMT

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.

Re: Loading from QVD - where condition based on a resident table

Wed, 05 Jun 2013 21:28:04 GMT

Thanks

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

Re: Loading from QVD - where condition based on a resident table

anandathome — Wed, 05 Jun 2013 21:31:04 GMT

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

Re: Loading from QVD - where condition based on a resident table

anandathome — Wed, 05 Jun 2013 21:34:09 GMT

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

Re: Loading from QVD - where condition based on a resident table

Wed, 05 Jun 2013 21:43:26 GMT

Thank You! That worked..