13 Replies Latest reply: Apr 28, 2015 9:43 AM by Yair Sharon RSS

    Data modeling

      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,

        • Re: Data modeling
          Anand Chouhan

          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;


          • Re: Data modeling
            Sunil Chauhan

            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,

            • Re: Data modeling
              pradeep t

              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)

              • Re: Data modeling
                Lohit Gudipati

                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,

                • Re: Data modeling
                  Anand Chouhan

                  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.

                  • Re: Data modeling
                    jagan mohan rao appala

                    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.

                    • Re: Data modeling

                      i tried to join by  conssioner then  its not giving correct data  month wise .. if i will make a key on the behalf  of conssioner and month then wat wil happen .  i dun want to concatente this tables bcoz they both have diffrent fields other dn month and conssioners

                      • Re: Data modeling

                        i more thing  these two sales of depratment 1 and 2 are different . i dont want to combine them .

                         

                        then what should i use . concatenate or join ??

                          • Re: Data modeling

                            depatment 1:

                            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,

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

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

                              //DATE_OF_DELIVERY,

                              //FLT_NO,

                                // AIRCRAFT_TYPE,

                               //  TO_DEST,

                                 QUANTITY as Sale,

                                

                                

                                  Month &'_'& Consessioner as Key,

                               //  BAY_NO,

                               // DISP_POSS_TIME,

                               // FUELLING_STARTED,

                                //FUELLING_FINISHED,

                               // DISP_DISPOSS_TIME,

                               // FUELLING_TIME,

                               // REMARKS,

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

                             

                                CATEGORY as Consessioner

                                 //BONDED_DUTY_PAID

                                 

                               FROM

                            SOurce XYZ,

                             

                             

                            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,

                            SALE1,

                            SALE2,

                                 //START_DATE as START_DATE_FF,

                                // END_DATE as END_DATE_FF,

                                 //INSERT_DATE,

                                 SERVICE_PROVIDER as Consessioner,

                                 // 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

                                Month &'_'& Consessioner as Key

                                

                             

                            FROM

                            Source 2.

                             

                             

                            but  its not working.. kindly help???