Best Way to Add Data to Table

[#1]

All,

I am reading in data from two separate databases which are identical and hence use Concatenate to join the two table.  However, this joined table also has a lot of extraneous records which need to be kept for historical purposes, but which are a waste and unneeded in the dashboard.  Unfortunately, there is no flag in the DB tables to indicate whether these SITE_CODEs are "active" or not.

I therefore exported the Concatenated table from QV to Excel and added a column to indicate whether a SITE_CODE was "active".  Now I can basically read in from the Excel table a list of "active" SITE_CODEs with which I would like to limit which records are imported from the two databases.  What is the best way of doing this?

I can think of a number of ways but I am pretty sure there will be a standard way of achieving this.

An example might be:

Import a table, Site_Details_Temp, from the manually maintained Excel file with only the valid / active SITE_CODEs.  The create your actual Site_Details table and in the load statement include a WHERE EXISTS (Site_Details_Temp_Site_Code,SITE_CODE).  Concatenate this with the load / sql statement for accessing the second DB and then Drop Table Site_Details_Temp.

Is this acceptable?  Could I do the same thing using a variable?  How do I assign the values to the variable if this is the case?

Thanks

Ian

[ToniKautto]

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.

[pat_agen]

hi Ian,

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.

[#4]

Guys,

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:

//Site_Details:

LOAD 

// 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 *

FROM GACWAREDB.dbo.SITE;

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?

[pat_agen]

hi,

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.