Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
brf10043
Creator
Creator

Where clause in Load Script reference Excel file

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]
);

 

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;


View solution in original post

6 Replies
Vegar
MVP
MVP

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;


brf10043
Creator
Creator
Author

Thanks Vegar.

 

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

 

Where EXISTS(IM_Component_link, IM_Component_link)

brf10043
Creator
Creator
Author

And yes the Field IM_Component_link does exists in the IM_Final.qvd
brf10043
Creator
Creator
Author

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.
brf10043
Creator
Creator
Author

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

Vegar
MVP
MVP

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

BR
Vegar