Data modeling

[#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,

[its_anandrjs · Accepted solution]

What happen when you join this two tables by field Month and Consessioner did you try or in association state also.

Depatment1:

Load * ,

Month &'_'& Consessioner

From Location;

Join// ( Inner Join, Outer Join)

Depatment2:

Load * ,

Month &'_'& Consessioner

From Location;

[SunilChauhan]

use left join as i can see department 1 should be you master data

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:

left JOIN(Depatment1)

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,

[SunilChauhan]

you can also apply anand suggetsion but you need to comment or rename Month and Consessioner in one of them other wise it will create synthetic key

[PradeepReddy]

Try to concatenate the tables, so that you will get the resultant data in single table.

Create a chart with..

Dimension:  Month

Expression : Sum(Sale)

[SunilChauhan]

i am afraid .concatenation will give duplicate rows most probably

[PradeepReddy]

either we can use the Join/Concatenation based on the requirements in other charts as well.

[#8]

Hi Yogita,

Concatenate both the tables, jus make sure the column names match

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,

concatenate

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,

[its_anandrjs]

Try this way previous suggestion is just sample code for syntax

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. If using joins create key field like  Month &'_'& Consessioner as %Key in both the tables.

[jagan]

Hi,

Try like this

Data:

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

FROM DataSource1;

Concatenate(Data)

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

FROM DataSource2;

Regards,

Jagan.