26 Replies Latest reply: Mar 18, 2016 4:20 AM by Avinash R RSS

    Data Model / Script help

    Amit Saini

      Hi Folks ,

       

      Below is my scenario:

       

      MKPF:

      LOAD //MANDT,

           MBLNR as Doc_Num_Key,

           BUDAT as %DATE_Key

      FROM

      $(vPath)MKPF.qvd

      (qvd);

       

      ----------------------------------------------------------------------------------------------------------

       

      LET vMinDate = num(makedate(2013));

      LET vMaxDate = num(today());

       

      Datefield:

      LOAD

          $(vMinDate) + IterNo() -1 as Datefield

      AUTOGENERATE (1)

      WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

       

      Calendar:

      LOAD

          Datefield as %DATE_Key,

          num(Datefield) as NumDate,

          date(Datefield, 'DD.MMM YYYY') as Date,

          year(Datefield) as Year,

          month(Datefield) as Month,

          'Q'&ceil(month(Datefield)/3) as Quarter,

          dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

          dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

          dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

          dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

          day(Datefield) as Day,

          week(Datefield) as Week,

          weekday(Datefield) as Weekday,

          weekyear(Datefield) as WeekYear

      RESIDENT Datefield;

       

      -------------------------------------------------------------------------------------------------

       

      T001W:

      LOAD

           WERKS as Plant,

           NAME1

      FROM

      $(vPath)T001W.qvd

      (qvd);

       

      This above data is coming from SAP and this below data I need to link from excel, which looks like below:

       

       

       

       

      for each plant in 'CWC','KDT','KLA','KMX','KOC','KSI','KTX','KUS'

       

      Data:

      LOAD [Local currency],

           Year

           [Week of],

           Plant as NAME1,

           [Value of material before counting],

           [Net Dollar Value of Inventory Adjustments],

           [Absolute Dollar Value of Inventory Adjustments],

           [Inventory Accuracy rate Net],

           [Inventory Accuracy rate Absolute],

           [Number of adjustments],

           FG,

           WIP,

           [FDM's],

           [Raw Material],

           Components

         

      FROM

      [.............$(plant)%20Inventory%20Accuracy.xlsx ]

      (ooxml, embedded labels, table is Data, filters(

      Remove(Row, Pos(Top, 3)),

      Remove(Row, Pos(Top, 2)),

      Remove(Row, Pos(Top, 1))

      ));

       

      next

       

       

       

      Linking should be:

       

      "Year" and "[Week of]" from Data table need to be linked with Calendar + "Plant" from Data need to be linked "NAME1" from T001W table

       

      I have simply tried renaming them as same name field , but this is not working , getting circular loop!

       

      Please suggest me possible best way , so that I can link Year ,Week of  fields from excel to SAP table + Data.Plant to T001W.NAME

       

      Thanks,

      AS

        • Re: Data Model / Script help
          Amit Saini

          Any Suggestion???

           

          Thanks,

          AS

          • Re: Data Model / Script help
            Avinash R

            In you case the excel file data will act as the linking table

             

            try like this

            1.first join or map the MKPF field to calendar table so you will have the one table

            2. After the combining this table build a key with the year &'-'& week  as Key

            3. In the Excel you have the year and Weekof so build a key like Year &'-'& [Week of] as Key

            4. finally link the T001W table using the Plant key which is already present in the excel

             

            now this doesn't form the circular key

              • Re: Data Model / Script help
                Amit Saini

                Plz help me with Script if possible!

                 

                Thanks,

                AS

                  • Re: Data Model / Script help
                    Avinash R

                    Try like this

                     

                     

                    MKPF:

                    LOAD //MANDT,

                         MBLNR as Doc_Num_Key,

                         BUDAT as %DATE_Key

                    FROM

                    $(vPath)MKPF.qvd

                    (qvd);

                     

                    ----------------------------------------------------------------------------------------------------------

                     

                    LET vMinDate = num(makedate(2013));

                    LET vMaxDate = num(today());

                     

                    Datefield:

                    LOAD

                        $(vMinDate) + IterNo() -1 as Datefield

                    AUTOGENERATE (1)

                    WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

                     

                    Join (MKPF)

                    Calendar:

                    LOAD

                        Datefield as %DATE_Key,

                        num(Datefield) as NumDate,

                        date(Datefield, 'DD.MMM YYYY') as Date,

                        year(Datefield) as Year,

                         year(Datefield)&'-'&week(Datefield) as Key,

                        month(Datefield) as Month,

                        'Q'&ceil(month(Datefield)/3) as Quarter,

                        dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

                        dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

                        dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

                        dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

                        day(Datefield) as Day,

                        week(Datefield) as Week,

                        weekday(Datefield) as Weekday,

                        weekyear(Datefield) as WeekYear

                    RESIDENT Datefield;

                     

                     

                    Data:

                    LOAD [Local currency],

                         Year&'-'&[Week of] as Key,

                         Year,

                         [Week of],

                         Plant as NAME1,

                         [Value of material before counting],

                         [Net Dollar Value of Inventory Adjustments],

                         [Absolute Dollar Value of Inventory Adjustments],

                         [Inventory Accuracy rate Net],

                         [Inventory Accuracy rate Absolute],

                         [Number of adjustments],

                         FG,

                         WIP,

                         [FDM's],

                         [Raw Material],

                         Components

                      

                    FROM

                    [.............$(plant)%20Inventory%20Accuracy.xlsx ]

                    (ooxml, embedded labels, table is Data, filters(

                    Remove(Row, Pos(Top, 3)),

                    Remove(Row, Pos(Top, 2)),

                    Remove(Row, Pos(Top, 1))

                    ));

                     

                     

                     

                    -------------------------------------------------------------------------------------------------

                     

                    T001W:

                    LOAD

                         WERKS as Plant,

                         NAME1

                    FROM

                    $(vPath)T001W.qvd

                    (qvd);

                      • Re: Data Model / Script help
                        Amit Saini

                        Sorry

                         

                        Still Circular loop!

                         

                        Thanks,

                        AS

                          • Re: Data Model / Script help
                            rgv rand

                            Try this but i am not sure

                             

                            i think you need to drop datefield after calendar creation and store Calendar into qvd.

                            • Re: Data Model / Script help
                              Avinash R

                              Sorry forgot to rename the Year

                               

                              MKPF:

                              LOAD //MANDT,

                                   MBLNR as Doc_Num_Key,

                                   BUDAT as %DATE_Key

                              FROM

                              $(vPath)MKPF.qvd

                              (qvd);

                               

                              ----------------------------------------------------------------------------------------------------------

                               

                              LET vMinDate = num(makedate(2013));

                              LET vMaxDate = num(today());

                               

                              Datefield:

                              LOAD

                                  $(vMinDate) + IterNo() -1 as Datefield

                              AUTOGENERATE (1)

                              WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

                               

                              Join (MKPF)

                              Calendar:

                              LOAD

                                  Datefield as %DATE_Key,

                                  num(Datefield) as NumDate,

                                  date(Datefield, 'DD.MMM YYYY') as Date,

                                  year(Datefield) as Year,

                                   year(Datefield)&'-'&week(Datefield) as Key,

                                  month(Datefield) as Month,

                                  'Q'&ceil(month(Datefield)/3) as Quarter,

                                  dual(year(Datefield)&'-'&'Q'&ceil(month(Datefield)/3),year(Datefield)&ceil(month(Datefield)/3)) as Year_Quarter,

                                  dual(year(Datefield)&'-'&month(Datefield),year(Datefield)&num(month(Datefield),'00')) as Year_Month,

                                  dual(year(Datefield)&'-'&week(Datefield),year(Datefield)&num(week(Datefield),'00')) as Year_Week,

                                  dual(year(Datefield)&'-'&month(Datefield)&'-'&day(Datefield),year(Datefield)&num(month(Datefield),'00')&num(day(Datefield),'00')) as Year_Month_Day,

                                  day(Datefield) as Day,

                                  week(Datefield) as Week,

                                  weekday(Datefield) as Weekday,

                                  weekyear(Datefield) as WeekYear

                              RESIDENT Datefield;

                               

                              drop table Datefield;

                              Data:

                              LOAD [Local currency],

                                   Year&'-'&[Week of] as Key,

                                   Year as Data_Year,

                                   [Week of],

                                   Plant as NAME1,

                                   [Value of material before counting],

                                   [Net Dollar Value of Inventory Adjustments],

                                   [Absolute Dollar Value of Inventory Adjustments],

                                   [Inventory Accuracy rate Net],

                                   [Inventory Accuracy rate Absolute],

                                   [Number of adjustments],

                                   FG,

                                   WIP,

                                   [FDM's],

                                   [Raw Material],

                                   Components

                               

                              FROM

                              [.............$(plant)%20Inventory%20Accuracy.xlsx ]

                              (ooxml, embedded labels, table is Data, filters(

                              Remove(Row, Pos(Top, 3)),

                              Remove(Row, Pos(Top, 2)),

                              Remove(Row, Pos(Top, 1))

                              ));

                               

                               

                               

                              -------------------------------------------------------------------------------------------------

                               

                              T001W:

                              LOAD

                                   WERKS as Plant,

                                   NAME1

                              FROM

                              $(vPath)T001W.qvd

                              (qvd);

                               

                               

                              NOTE: do you have any other tables in the script