Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
you might want to consider Autonumber?
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
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.
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
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
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 ?????????????????