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

[antoniotiman]

Hi Amit,

You must use NoConcatenate for Table PPMDeliveries..

PPMDeliveriesExport:

NoConcatenate

Load *,

.....;

Drop Table RQMS_STAMM;

Regards,

Antonio

[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

[antoniotiman]

Amit,

try with Preceding Load.

[tresesco]

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

[its_anandrjs]

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

];

[amit_saini]

Tresesco,

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

num(Month(DTRQDATUM)) as RQMS_Month

Thanks,
AS

[tresesco · Accepted solution]

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

;