7 Replies Latest reply: Jul 16, 2014 6:31 AM by Anand Chouhan RSS

    Script Help ????

    Amit Saini

      Hi Folks ,

       

      Please see script below:

       

      I am having trouble creating new fields by loading a RESIDENT table and  then adding a new field by reformatting dates , looks like something is wrong load is running for last 3 HRs and if I'm loading upper table only it is taking less than 5 mints to load all data. Something is wrong when I'm calling PPMDeliveriesExport  table

       

      RQMS_STAMM:

      LOAD

          //KEYS

          NRQNR as %RQMS_STAMM_Key,

          NRQNR as %SPR_STAMM,

          if(NREKLQUELLE<>1,NKUNDENNR) as FA_STAMM,

          NLFDMANDNR as %MANDANT_Key,

          date(floor(DTRQDATUM), 'DD.MM.YYYY') as DATUM,

          Year(DTRQDATUM) as RQMS_Year,

          num(Month(DTRQDATUM)) as RQMS_Month,

          date(floor(DTABSCHLUSS), 'DD.MM.YYYY') as Closure_Date,

          if(isnull(DTABSCHLUSS),0,1) as tmp_Closure_Flag,

          if(isnull(DTABSCHLUSS),1,0) as tmp_Open_Flag,

          NDTABSCHLUSSTZID,

          num(floor(DTRQDATUM)) as NumberDate,

          num(NTTTYPE,'00') as %QS_TXT,

          NTTTYPE,

          //NLFDZUSINFO1NR as %RQMS_ZUSINFO1_Key,

          //FIELDS

          NRQNR as NRQNR,

          //'1' AS NRQZL, -> not possible due to join with RQMSPOS later

          NKUNDENKEN as Complaint_Group,

          SRQNR as Complaint_Number,

          NREKLQUELLE as Complaint_Type,

          SBEMEINGABE as Complaint_Header,

          LSBEMKUNDENDATEN as Complaint_Description,

          SFELDNR1 as Complaint_Description_ENG,

          applymap('COMPLAINT_TYPE_MAPPING',  upper(NREKLQUELLE), 'No Complaint Type') as Complaint_Type_Description,

          applymap('RQMS_ZUSINFO1_MAPPING', NLFDZUSINFO1NR) as Platform,

          //CHX PRAGYA 15-10-2013 include Cost

          //APPLYMAP('RQMS_KOSTEN_MAPPING', NRQNR) as Cost,

          DTRQDATUM,

          //Chx Pragya 10-03-2011 for Cost center name in Internal type complaint.

          if(NREKLQUELLE=1, applymap('KOSTENSTELLEN_MAPPING', NKUNDENNR)) as Cost_Center_for_Internal_complaints,

          if(NREKLQUELLE=1, applymap('KOSTENSTELLEN_MAPPINGID', NKUNDENNR)) as Cost_Center_for_Internal_complaints_ID,   

          if(isnull(NREKLANERKANNT),'NULL',NREKLANERKANNT) as Complaint_Acceptance,

          applymap('PERS_USER_MAPPING', upper(NKOORDPERSNR)) as Complaint_Owner,

          applymap('PERS_TEL_MAPPING', upper(NKOORDPERSNR)) as Complaint_Telephone,

          // Chx Schroeder 01-08-2011 insert update date for flash report

          applymap('Complaint_Update_Mapping', NRQNR) as Complaint_Update_Date,

          NREKLKOSTEN,

          NSCHROTTFLAG,

          SKUNDENANSPRECH

          

      FROM \\TXKAPPHUT003\SourceDocuments\qvd\RQMS\RQMS_STAMM.qvd (qvd);

       

       

      PPMDeliveriesExport:

      load *

      , '1/1/' & Year([DTRQDATUM])  as Year

      , if(Month(DTRQDATUM) = 'Jan',1,if(Month(DTRQDATUM)='Feb',2,if(Month(DTRQDATUM)='Mar',3,if(Month(DTRQDATUM)='Apr',4,if(Month(DTRQDATUM)='May',5,if(Month(DTRQDATUM)='Jun',6,if(Month(DTRQDATUM)='Jul',7,if(Month(DTRQDATUM)='Aug',8,if(Month(DTRQDATUM)='Sep',9,if(Month(DTRQDATUM)='Oct',10,if(Month(DTRQDATUM)='Nov',11,if(Month(DTRQDATUM)='Dec',12)))))))))))) & '/1/' &  Year(now()) as Month

      RESIDENT RQMS_STAMM

      where

      Year([DATUM]) = 2014

      and Month([DATUM]) = 'Jun'

      and  Day([DATUM]) =  5

      ;

       

       

       

      Kindly help!

       

      Thanks,

      AS

        • Re: Script Help ????
          Antonio Mancini

          Hi Amit,

          You must use NoConcatenate for Table PPMDeliveries..

           

          PPMDeliveriesExport:

          NoConcatenate

          Load *,

          .....;

           

          Drop Table RQMS_STAMM;

           

          Regards,

          Antonio

            • Re: Script Help ????
              Amit Saini

              Antonio,

               

              Thanks for reply but looks like not working, load is running from last 20 mints and it got struck  , even no error in log file.

               

              Thanks,
              AS

                • Re: Script Help ????
                  Antonio Mancini

                  Amit,

                  try with Preceding Load.

                  • Re: Script Help ????
                    Tresesco B

                    One improvement point:

                     

                    Why do you need ' ..........

                    if(Month(DTRQDATUM) = 'Jan',1,if(Month(DTRQDATUM)='Feb',2,if(Month(DTRQDATUM)='Mar',3,if(Month(DTRQDATUM)='Apr',4,if(Month(DTRQDATUM)='May',5,if(Month(DTRQDATUM)='Jun',6,if(Month(DTRQDATUM)='Jul',7,if(Month(DTRQDATUM)='Aug',8,if(Month(DTRQDATUM)='Sep',9,if(Month(DTRQDATUM)='Oct',10,if(Month(DTRQDATUM)='Nov',11,if(Month(DTRQDATUM)='Dec',12)))))))))))) & '/1/' &  Year(now()) as Month

                     

                     

                    When the same can be got simply like:

                    Num(Month(DTRQDATUM)) as MonthNum

                      • Re: Script Help ????
                        Amit Saini

                        Tresesco,

                         

                        I already did it ,as you can see in script in my post:

                        num(Month(DTRQDATUM)) as RQMS_Month

                         

                        Thanks,
                        AS

                          • Re: Script Help ????
                            Tresesco B

                            It seems that you can get what you want in the first load itself like:

                             

                            RQMS_STAMM:

                            LOAD

                                //KEYS

                                NRQNR as %RQMS_STAMM_Key,

                                NRQNR as %SPR_STAMM,

                                .....

                                applymap('Complaint_Update_Mapping', NRQNR) as Complaint_Update_Date,

                                NREKLKOSTEN,

                                NSCHROTTFLAG,

                                SKUNDENANSPRECH,

                                 Year(now()) as Month         // the only extra field you wanted in the resident load

                             

                                

                            FROM \\TXKAPPHUT003\SourceDocuments\qvd\RQMS\RQMS_STAMM.qvd (qvd)

                            where

                            Year([DATUM]) = 2014

                            and Month([DATUM]) = 'Jun'

                            and  Day([DATUM]) =  5

                            ;

                    • Re: Script Help ????
                      Anand Chouhan

                      You can map the month values as num also by using inline table

                       

                      Ex:-

                       

                      Mapping Load * Inline

                      [

                      Month,MOnthNum

                      Jan,1

                      Feb,2

                      Mar,3

                      Apr,4

                      May,5

                      June,6

                      July,7

                      Aug,8

                      Sep,9

                      Oct,10

                      Nov,11

                      Dec,12

                      ];