Data Model related Query

[#1]

Hi ,

I have two tables  and  one table is having data of 1st department and  another table is having data of  2nd depatment .

there are two  field are common in both

month and conseioners name   .

and i want to combine  these two show total sale of both the depatment  month wise and conssioners wise,

Deparment 1:

how should i combine these two tables so that there is loop in this and will show perfect data model ?

Depatment1:

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

     SALE

     START_DATE as START_DATE_FF,

     END_DATE as END_DATE_FF,

     INSERT_DATE,

     SERVICE_PROVIDER as Consessioner,

Department 2:

Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

Sale,

CATEGORY as Consessioner,

[#2]

you might want to consider Autonumber?

[aveeeeeee7en]

There are 2 ways to do this:

1) First Method. Use Link Tables:

Depatment1:

Load

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

     SALE

     START_DATE as START_DATE_FF,

     END_DATE as END_DATE_FF,

     INSERT_DATE,

     SERVICE_PROVIDER as Consessioner,

(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY'))&'/'&SERVICE_PROVIDER AS Comination_Key,

'Table1' AS Flag1

From .......qvd;

Department 2:

Load

Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

Sale,

CATEGORY as Consessioner,

(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY'))&'/'&CATEGORY AS Comination_Key,

'Table2' AS Flag2

From .......qvd;

Use Combination_Key to show Total Sales and use Flag1 & Flag2 to see data coming from which Table.

2) Second Method. Use Concatenated Table:

Depatment1:

Load

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

     SALE

     START_DATE as START_DATE_FF,

     END_DATE as END_DATE_FF,

     INSERT_DATE,

     SERVICE_PROVIDER as Consessioner,

(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY'))&'/'&SERVICE_PROVIDER AS Comination_Key,

'Table1' AS Flag1

From .......qvd;

Concatenate

Department 2:

Load

Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

SALE,

CATEGORY as Consessioner,

(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY'))&'/'&CATEGORY AS Comination_Key,

'Table2' AS Flag2

From .......qvd;

Hope that works for you.

Regards

Aviral Nag

[its_anandrjs]

Try one of this ways

Depatment1:

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as MonthField,

     SALE

     START_DATE as START_DATE_FF,

     END_DATE as END_DATE_FF,

     INSERT_DATE,

     SERVICE_PROVIDER as ConsessionerField,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) &'_'& SERVICE_PROVIDER as %Key

Join or Concatenate here depends on the table behavior according to that you have to use join with(inner,Outer,Left,Right) or Cocatenate

Department 2:

Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Depart_Month,

Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) &'_'& CATEGORY as %Key

Sale,

CATEGORY as Depart_Consessioner

Note:- If use concatenate the fields name same but not necessarily but you have to check out put of the table and the field name in the table must be same name. If using joins create key field like  Month &'_'& Consessioner as %Key in both the tables.

[SunilChauhan]

if  second tables Consessioner field is subet of firstable  Consessioner field

then use left join  b/w two tables.

or  you can use what anand chouhan suggeted but keep to seprate table  department 1 linked to other with composite key

or

concatenate in this case if both table have same Consessioner then might duplicate

or Most used

create a fact table using tables dept 1 and dept2  with composite key  and link these tables to this fact table

its makes star schema for you. you need to take care of synthetic key

hope thishelps

[#6]

i have used this % key concept   everything is  correct  but when i m taking sum(Sale)

its not working for dep 1.

its working when i'm making graph by conssioners

[#7]

DEp1:

LOAD
Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,
Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month123,
Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' & mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) &'_'& CATEGORY as %Key,
QUANTITY  as sale for dep1,
CATEGORY as Consessioner,

FROM
[ABC]
(qvd);


Join

dep 2:

LOAD
Month(Date#(MONTH_YEAR_OF_SALE, 'MMM YYYY')) &'_'& SERVICE_PROVIDER as %Key,


DUTY_PAID_SALE as sale 1

BONDED_SALE as sale 2
SERVICE_PROVIDER as Consessioner,
TERMINAL
FROM
[GYH]

see my final script .

now see  in dep 2 there are two sales field   and  by addibd both i will get my total sale

so when  im making graph  on behalf of conseioners its giving correct data

like foe consessioner A:12

               //                     B:124

                                      C:123

but i'm using exp like

sum ( sale1 ) + sum(sale2)

its giving some rendom number

so wat could be the issue

please help ?????????????????