1 Reply Latest reply: Sep 29, 2011 1:30 AM by sandeepa rout RSS

    Joining Issues

    sandeepa rout

      Hi All,

       

      Please find attached of the screen shot of my schema.

       

      In this screen shot you can able to see there are 4 tables.

      1- IMS2010   2-IMSReRun   3-IMSCalendar    4-Brand Data

       

       

      IMS2010 is coming from IMS Data Source and IMSReRun table is coming from SLS DataSource. It has same sets of field.

       

      I have to compare each other fields in regards of ProdCode so that one could easily know which data is missing in which DataSource.

       

      By keeping it simple I have to compare the data of IMS2010 and IMSReRun against ProdCode.

       

      Hope it makes sense.

       

      Please suggest .....!

       

      Many Thanks,

      Sandeepa

        • Re: Joining Issues
          sandeepa rout

          Please See the script.

           

           

          Brand:

          LOAD item_code ,

               item_code as ProdCode,

               sales_stats_group_code as BrandCode,

               stats_group_desc as BrandName

          FROM

          C:\Users\sandeepa_rout\Desktop\Brand_Data.txt

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

           

           

           

          ************************************************************************************************************************************

          (Data from DataSource1)

           

          IMS:

          Directory;

          LOAD @1 as IMS.CustCode,

               @2 as IMS.TransType,

               @3 as InvoiceNo,

               @4,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,

               MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek

          FROM

          [ExtractedIMSFiles(headers and details\Header\2010\07\271.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          HeaderJune281:

          Directory;

          Join

          LOAD @1 as IMS.CustCode,

               @2 as IMS.TransType,

               @3 as InvoiceNo,

               @4,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,

               MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek

          FROM

          [ExtractedIMSFiles(headers and details\Header\2010\07\281.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          HeaderJune291:

          Directory;

          Join

          LOAD @1 as IMS.CustCode,

               @2 as IMS.TransType,

               @3 as InvoiceNo,

               @4,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,

               MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek

          FROM

          [ExtractedIMSFiles(headers and details\Header\2010\07\291.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          HeaderJune301:

          Directory;

          Join

          LOAD @1 as IMS.CustCode,

               @2 as IMS.TransType,

               @3 as InvoiceNo,

               @4 ,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,

               MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek

          FROM

          [ExtractedIMSFiles(headers and details\Header\2010\07\301.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          DetailJune271:

          Directory;

          Join

          LOAD @1 as ProdCode,

          @1 as IMS.ProdCode,

               @2 as IMS.Qty,

               @3 as IMS.LineValue,

               num(round(@3, 1234.12)) as IMS.Amount,

               @4 as IMS.InvType,

               @5 as InvoiceNo

          FROM

          [ExtractedIMSFiles(headers and details\Detail\2010\07\271.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          DetailJune281:

          Directory;

          Join

          LOAD @1 as ProdCode,

          @1 as IMS.ProdCode,

               @2 as IMS.Qty,

               @3 as IMS.LineValue,

                num(round(@3, 1234.12)) as IMS.Amount,

               @4 as IMS.InvType,

               @5 as InvoiceNo

          FROM

          [ExtractedIMSFiles(headers and details\Detail\2010\07\281.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          Detail291:

          Directory;

          Join

          LOAD @1 as ProdCode,

          @1 as IMS.ProdCode,

               @2 as IMS.Qty,

               @3 as IMS.LineValue,

                num(round(@3, 1234.12)) as IMS.Amount,

               @4 as IMS.InvType,

               @5 as InvoiceNo

          FROM

          [ExtractedIMSFiles(headers and details\Detail\2010\07\291.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          Detail301:

          Directory;

          Join

          LOAD @1 as ProdCode,

          @1 as IMS.ProdCode,

               @2 as IMS.Qty,

               @3 as IMS.LineValue,

                num(round(@3, 1234.12)) as IMS.Amount,

               @4 as IMS.InvType,

               @5 as InvoiceNo

          FROM

          [ExtractedIMSFiles(headers and details\Detail\2010\07\301.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          *************************************************************************************************************************************

          (Data from DataSource2)

           

          IMSReRun:

          Directory;

          LOAD @1 as CustCode,

               @2 as TransType,

               @3 as InvoiceNo,

               @4 as InvDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,

               MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as InvoiceDate,

               Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSReRunWeek

           

          FROM

          [IMS-ReRun\TR727291-Header.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          Detail:

          Directory;

          Join

          LOAD @1 as ProdCode,

               @1 as IRR.ProdCode,

               @2 as Qty,

               @3 ,

               num(round(@3, 1234.12)) as Amount,

               @4 as InvType,

               @5 as InvoiceNo

          FROM

          [IMS-ReRun\TR727291-Detail.txt]

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

           

          ************************************************************************************************************************************

          (Calendar)

           

          Data:

          LOAD

          // Year,

              // Week,

              [Start Date] as datum,

               [Start Date],

               [End date]

          FROM

          C:\Users\sandeepa_rout\Desktop\Book1.xlsx

          (ooxml, embedded labels, table is ImsCalendar);

           

           

           

           

           

           

          StartCalendar:

          LOAD

                    Date(Floor(datum)) as datum

          Resident Data;

           

           

          Master_Calendar:

          LET varMinDate = num(peek('datum' , 0 , 'StartCalendar'));

          LET varMaxDate = num(peek('datum' , -1 , 'StartCalendar'));

          LET vToday=num(today());

          LET MinDate = peek('datum' , 0 , 'StartCalendar');

          LET MaxDate = peek('datum' , -1 , 'StartCalendar');

          LET vstart=date(0);

           

           

          Temp_Calendar:

          LOAD $(varMinDate) + rowno()-1   AS Num,

          date($(varMinDate) + rowno()-1)  AS TempDate

          AUTOGENERATE $(varMaxDate)-$(varMinDate) +1;

           

           

          Master_Calendar:

           

           

          LOAD

          TempDate,

          TempDate     AS datum,

          week(TempDate)    AS Week,

          year(TempDate)      AS Year,

          month(TempDate)     AS Month,

          day(TempDate)       AS Day,

          weekday(TempDate)   AS WeekDay,

          'Q' & ceil(month(TempDate) / 3)    AS Quarter,

          date(monthstart(TempDate), 'MMM-YYYY')    AS MonthYear,

          date(monthstart(TempDate), 'YYYY-MM')     AS YearMonth,

          week(TempDate) & '-' & year(TempDate)      AS WeekYear,

          inyeartodate(TempDate, $(vToday),0)*-1       AS CurYTDFlag,

          inyeartodate(TempDate,$(vToday),-1) * -1    AS LastYTDFlag

           

           

          RESIDENT [Temp_Calendar]

           

           

          ORDER BY TempDate asc;

           

           

          DROP TABLE Temp_Calendar,StartCalendar;

           

           

          ******************************************************************************************************************************************

           

          Please suggest how to compare ProdCode of both the data sources..

           

          Thanks