Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)"
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);
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
How are you creating your variable? What's the source of the variable and how will that be maintained?
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
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);
May be read about where exists here: