chart dimension for 2nd expression

[#1]

Hi Gurus,

Please somebody can help me with this...

I have 3 tables:

Master Calendar having calendar_year as column

I have trades table with Trades_year as one of the column and other columns

I have trades_2 table with Trades_2_year as one of the column and other columns.

Trades and Trades_2 are linked with a column called code.

I am trying to prepare a chart with Trades_year as dimension and want to show sum(Trades_value) as one expression and sum(Trades_2_value) as another expression. for me sum(Trades_Value) is showing correct and Sum(Trades_2_value) is showing wrong.

Objective is to show yearly sum(values) from Trades and Trades_2 tables.

Thanks in advance...

[Jason_Michaelides]

Can you post your app or data model image?

I think you have run into the classic issue of multiple, similar fact tables (Trades_1 and Trades_2 in this case) and therefore you have multiple date fields etc when you need only one.

Try concatenating your Trade tables together (stick one on top of the other, with all common fields aligned):

Trades:

LOAD

     'Trade 1'          AS     TradeType

     Trades_Year   AS     Calendar_year

     ,*

FROM Trades_1;

CONCATENATE(Trades)

LOAD

     'Trade 2'               AS     TradeType    

     Trades_2_Year   AS     Calendar_year

     ,*

FROM Trades_2;

This will also force the year fields to join with the calendar (I'm assuming there are no other common fields with the master calendar)

Hope this helps,

Jason

[#3]

Hi Jason,

Thanks for your response.

In my application i am using section access to restrict the data on the Firm_Key in Trades table. The moment i link this table to any other table having full data, the other tables also get reduced as per the matching records of Trades table even though those tables do not have Firm_key. Hence i have kept trades table as separate without any link to full data table. If section access restrict only the records of the table having the key, then i think i can resolve the issue.

Regards,

Abdul

[Jason_Michaelides]

You may need to create a link table with all the Firm_key and Code values etc and change your section access to look at this.

You should always strive to have a simple star schema for your data model and not duplicate fields that are really the same.

Jason