Splitting one table into several

[#1]

Hi,

I have an app which is based on one table. This table consists of several different dates for different statuses of an object. What i want to do here is to be able to change the filters on one instance without having it resonate throug to the others.

For instane; if i change the pending year to 2012, i dont want the years for received and filed to change. I have spoken to colleagues about this and they suggest to slip up my one table I have today into four others, one holding the recieved dates, one holding the pending, one for the filed, and then lastly one for just the ID-key (A1, A2, A3, ....).

As an added bonus to this, is still have one field "Status" which i would like to resonate throug all three charts though. if i change the status to "Rejected" for instance, I want it to show only the rejected objects across the board. Any ideas?

Cheers,

Nils

[swuehl]

You can load your source data to several tables linked by status:

NAME:

LOAD name,

     status

FROM

(biff, embedded labels, table is TestExport$);

RECEIVED:

LOAD status,

     received_date,

     received_year,

     received_month,

     received_week

FROM

(biff, embedded labels, table is TestExport$);

PENDING:

LOAD

     status,

     pending_date,

     pending_year,

     pending_month    

FROM

(biff, embedded labels, table is TestExport$);

FILED:

LOAD

     status,

     filed_date,

     filed_year,

     filed_month

FROM

(biff, embedded labels, table is TestExport$);

This should do what you describe above, so I am not sure if this is what you really need. You could also look into alternate states (since QV11) or set analysis if you want to disregard certain selections in your analysis.

[#3]

sadly that answer will not let me calculate the graphs since they are based on name. i added a specific name for the differnet loads lika below, but the different graphs are still affected by changing the other graphs. I need to ba able to change the year of the pending graph (for instance) without it affecting the other graphs.

LOAD
  name as pend_name,
  status,
    pending_date,
    pending_year,
    pending_month
FROM

(biff, embedded labels, table is TestExport$);

[#4]

Hi,

Try to use SET analysis in your COUNT expression as below:

For Pending Chart:
COUNT({<received_year=, filed_year=, pending_year = {'>0'} >} DISTINCT name)

For Received Chart:

COUNT({<received_year= {'>0'}, filed_year=, pending_year = >} DISTINCT name)

For Field Chart:

COUNT({<received_year=, filed_year= {'>0'}, pending_year = >} DISTINCT name)

[#5]

hi,

that still didnt cut it im afraid. if i try to change the values for year for pending/filed/received it affects values in the other tables as well. i am also getting a significant slowdown in loading the tables.

any thoughts?

[swuehl]

Your tables are still linked via status, so whenever a selection in e.g. pending year limits the available set of status values, the other charts will be affected.

[#7]

yes, and as i wrote in my original post is that i need the tables to remain the same for the different years, however they all need to change for status. if this possible?

i have been thinking about keeping the tables unlikned, but input a button for the status. i would then create different instances fro the different statuses: rec_status; filed_status; pend_status.

if one then were to click the "approved" status the button would the set the there statuses above to "approved" - you think that might work?

[swuehl · Accepted solution]

Could be possible e.g. using alternate states, as I mentioned in my first post.

Have you looked into that?

See attached a sample:

[#9]

wow! this actuall looks exactly lika what i want to do !!

i need to run some tests on this first but i think this might be it - great work!