topic Re: Excluding Multiple Values from a QVD Load in QlikView

Excluding Multiple Values from a QVD Load

Fri, 07 Oct 2016 18:53:32 GMT

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)"

Re: Excluding Multiple Values from a QVD Load

sunny_talwar — Fri, 07 Oct 2016 18:56:44 GMT

Try using Where Exist instead of what you are doing

LOAD * INLINE [

clinet_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 not Exists(client_id);

Re: Excluding Multiple Values from a QVD Load

Fri, 07 Oct 2016 19:03:19 GMT

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

Re: Excluding Multiple Values from a QVD Load

sunny_talwar — Fri, 07 Oct 2016 19:06:42 GMT

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

Re: Excluding Multiple Values from a QVD Load

Fri, 07 Oct 2016 19:17:36 GMT

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

Re: Excluding Multiple Values from a QVD Load

sunny_talwar — Fri, 07 Oct 2016 19:25:43 GMT

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;

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

Re: Excluding Multiple Values from a QVD Load

sunny_talwar — Fri, 07 Oct 2016 19:26:24 GMT

May be read about where exists here:

QlikView Maven: QlikView Exists Function