Connecting two tables using date key

[jegadeesan]

Hi All,

Am getting problem in retrieving rows from one table using date key of another table... This is how i made an association between tables...

Table A                          Table B                          Table C

Date,                             Product code,                  Date,

Sale,                             SAP Code ,                     Sales,

Product code,                 Brand,                            Product,

Quantity,                                                              SAP Code

Volume,

using Product code (Table A )  joined with (Table B )

using SAP code (Table B) joined with (Table C)

While I trying to see the sum of particular year sale from Table A using Date of Table C,  getting wrong sum of sale.

Can anyone help me out of this issue.....

Regards,

Jegadeesan G

[satheshreddy]

Hi Jagadeesan,

your getting wrong some why because, your data having two sales columns 1 is sales and 2 nd one is sale first you have to check data model if you are written

1). sum(sales) you will get Table C sales field sum.

2). sum(sale)  you will getting Table1 Sale sum

please check and change the names same you will get correct sum.  

Regards
Sathsih

[jegadeesan]

Hi sathish...

Table A has internal sales and data table C has third party vendor provided sales data to the same company..

Am trying to ensure the data provided by third party vendor is in line with Internal sales data.... Calculation is like   Vendor sales data / Internal sales data.... That i have to show in Trend line... am in the situation to use only one date dimension either from Table A or Table C.... When I am using table A Date dimension, internal sales correct.... It happens vice versa to table C as well.... Table C source file is in CSV format...

Regards,

Jegadeesan G

[satheshreddy]

Hi Jaga,

so you can create Calendar and perform the options.

Regards

Sathish

[kumarravi]

Hey ,

(For reference I will use only 2 tables of yours, it is very similar for rest)

You can follow the following steps to do this : 

Step 1 : Create Date, month, year column using Date column in each table

Posting Table: 

Date([Date]) as Posting_date,

Month(Date([Date])) as Posting_month,

Year(Date([Date])) as Posting_year

Expense Table :

Date([Voucher Date]) as Expense_date,

Month(Date([Voucher Date])) as Expense_month,

Year(Date([Voucher Date])) as Expense_year

Step 2: Create a Link Table

Link_table_1 : 

Load Distinct 

Posting_date as Date,

Posting_month as Month,

Posting_year as Year

Resident [Posting table];

Concatenate Link_table_1

Load

Expense_date as Date,

Expense_month as Month,

Expense_year as Year

Resident [Expense table];

NoConcatenate

Step 3: Create a new table and drop the older one

LINK_TABLE:

Load

Date,

Month,

Year

Resident Link_table_1;

Drop Link_table_1;