Re: Excluding Multiple Values from a QVD Load - Qlik Community

Report Inappropriate Content · ‎2016-10-07

I am loading summarized data from a QVD in my script. In my case the QVD has multiple Client_ID's in it. In my Data Model I load all of these Client_ID's, but I am trying to create a separate data set and want to be able to exclude multiple Client_ID's from this load statement.

Below is an example of code that I have gotten to work, as long as my variable @BOBExclusionClient_ID only contains one Client_ID.

	LOAD
	Col1 as "Total_Column",
	SUM(X) as "Total_X",
	SUM(Y) as "Total_Y",
	SUM(Z) as "Total_Z",
	FROM
	$(vQVDPath)\Utilization.QVD (qvd)
	WHERE client_id <> $(@BOBExclusionClient_ID);

However, I want this code to work (without manual intervention) if I need to exclude multiple Client_ID's from my load. I was anticipating the @BOBExclusionClient_ID variable to hold a comma separated list of Client_ID's that I would use like:
"where client_id not in (1,2,3)"

sunny_talwar · ‎2016-10-07

Try using Where Exist instead of what you are doing

LOAD * INLINE [

clinet_id

1

2

3

4

];

LOAD Col1 as "Total_Column",

SUM(X) as "Total_X",

SUM(Y) as "Total_Y",

SUM(Z) as "Total_Z",

FROM$(vQVDPath)\Utilization.QVD (qvd)

WHERE not Exists(client_id);

Report Inappropriate Content · ‎2016-10-07

My dilema is that the list of clients is constantly growing, so I don't want to hardcode a load statement to use that. I fee like loading a "temp" table with just the client_id's that I care about is the same issue as the load statement

sunny_talwar · ‎2016-10-07

How are you creating your variable? What's the source of the variable and how will that be maintained?

Report Inappropriate Content · ‎2016-10-07

Using a MustInclude File.

The Utilization.QVD contains data for all Client_ID's that are in the origin data. However, we are manually maintaining the list of client_id's to exclude. I guess we could just update the INLINE statement instead of adding that ID to the include file

sunny_talwar · ‎2016-10-07

Or you can maintain the list in an Excel file. The table doesn't have to be Inline, it can be from any data source

LOAD clinet_id

FROM ExcelFile.xlsx;