topic Re: Where clause in Load Script reference Excel file in QlikView

Where clause in Load Script reference Excel file

brf10043 — Sat, 16 Nov 2024 04:31:19 GMT

All,

I have the following load script to load incident data

LOAD
*,
'1' as Ticket_Count,
[IM Submitter RA] as RA_XREF
FROM [$(Path)IM_Final.qvd](qvd);

But I only want to load incidents from the qvd where the value in the IM_Component_link exists in mapping table that I am loading from Excel. How do I accomplish this?

LOAD COMPUTER_SYSTEM_CI_ID,
[Collection Name],
Subcollections_CI_ID,
Subcollections_NAME,
COMPUTER_SYSTEM_CI_ID1 as IM_Component_link,
COMPUTER_SYSTEM_NAME,
SITE,
ASSET_LIFECYCLE_STATUS,
ENVIRONMENT,
CS_OPERATING_SYSTEM,
INSTANCE_ID,
DB_CI_ID,
DB_NAME,
DB_MODEL,
DB_VERSION_NUMBER,
DB_ENVIRONMENT
FROM
[$(PathEx)Critical Apps Map.xlsx]
(ooxml, embedded labels, table is [686 Coll_Server_DB]);

Re: Where clause in Load Script reference Excel file

Vegar — Thu, 07 Feb 2019 21:05:22 GMT

IM_Component_links:
LOAD
IM_Component_link,
FROM
[$(PathEx)Critical Apps Map.xlsx]
(ooxml, embedded labels, table is [686 Coll_Server_DB]);

LOAD
*,
'1' as Ticket_Count,
[IM Submitter RA] as RA_XREF
FROM [$(Path)IM_Final.qvd](qvd)
Where EXISTS(IM_Component_link, IM_Component_link)
;
Drop table IM_Component_links;

Re: Where clause in Load Script reference Excel file

brf10043 — Thu, 07 Feb 2019 21:22:01 GMT

Thanks Vegar.

I'm getting a "Field 'IM_Component_link' not found" with the Where clause.

Where EXISTS(IM_Component_link, IM_Component_link)

Re: Where clause in Load Script reference Excel file

brf10043 — Thu, 07 Feb 2019 21:23:52 GMT

And yes the Field IM_Component_link does exists in the IM_Final.qvd

Re: Where clause in Load Script reference Excel file

brf10043 — Thu, 07 Feb 2019 21:29:05 GMT

Hold on. Actually I am wrong. Field IM_Component_link does not exists in the IM_Final.qvd. I create it in a link table later in the load.

Re: Where clause in Load Script reference Excel file

brf10043 — Thu, 07 Feb 2019 21:43:48 GMT

Thanks Vegar. Your solution works perfectly. I had a problem with renamed columns.

Re: Where clause in Load Script reference Excel file

Vegar — Thu, 07 Feb 2019 21:56:51 GMT

Oh, I missed your earlier comments.
I'm glad it worked out for your.

BR
Vegar