Not sure that you really need to do the manual excel work around. Do you have a static set of SITE_CODES that you map as active or inactive? If yes, then you likley could solvbe this by applying a maping table in QV. This will be easier to evaluate if you can provide a sample QVW with some example datat loaded fom your data base, along with the values you manually add in Excel.
As usual any solution that matches your needs and provided the accurate result could be acceptable, then you might always be able to find a more optimal solution.
are you still referring to your two different databses (the historical one and its replacement)? there are lot sof things you could do in that scenario but perhaps your starting point should be to create qvds from your original data sources.
Then you can work on your data without having to run sql statements and reload everything from scratch.
Now for this specific question which is really about reducing the amount of data you pull into your document the best practice would be to use the exists() clause as you describe above. Much better than a variable and compatible with enhanced qvd loading which will change your life from the 2h30 wait for loading 900k records you have described elsewhere.
If I read your post correctly you offloaded all your data into xl then added an "active" flag to each line. You could do this with just one line per site code, you don't need every single transacrion line. Make this xl file your Master Data for your "site" dimension, their might be other useful info you would like adding to your Site code and here would be the place to do it.
Thanks for the input . . . the Sites table is actually quite small - maybe 150 records of which about 50 are active. Essentially, all I have done is created a simple export of the SITE_CODE and asked the business to indicate which are active. I then created a new column in the Excel spreadsheet called Active and marked those SITE_CODEs which were active as 'Y'.
I then created a simple one column ([Site_Details_Temp SITE_CODE]) table called Site_Details_Temp and using a where clause on <Active='Y'>. Then on connecting to the two databases, I perform the following:
// Load Table Keys
SITE_CODE As [Key Site_Details SITE_CODE],
// Load Table Data
SITE_CODE As [Site_Details SITE_CODE],
SITE_IND As [Site_Details SITE_IND],
SITE_TYPE As [Site_Details SITE_TYPE],
SITE_NAME As [Site_Details SITE_NAME],
'GACware (Dubai)' As [Site_Details Data Source],
SITE_NAME As [Site_Details Site Code - Dubai]
Where Exists ([Key Site_Details_Temp SITE_CODE],SITE_CODE) ;
SQL SELECT *
This limits the SITE_CODEs being imported to only those that already exist in the [Site_Details_Temp SITE_CODE] column, effectively ensuring that I only have active sites in the dashboard. I concatenate between the two database connects.
Does this make sense?
yes it does.
Your reduction is from 150 to 50 records. This will be worthwhile when you apply the data reduction to the transaction tables you load into your qvw.
For example if you are loading your sales or shipments or whatever you are building your dashboards about, I supose this data gets loaded in with a field called [Key Site_Details SITE_CODE]. Are there transactions like this? do you want to see them? If there are and you don't then you can use your exists() clause again to exclude them.
If there are transactions with inactive site codes and your tables are joined via the
[Key Site_Details SITE_CODE]. key and you don't exclude them you will get extra site codes but which wont have nay site info (name etc.) attached to them.