Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
Hi Amit,
You must use NoConcatenate for Table PPMDeliveries..
PPMDeliveriesExport:
NoConcatenate
Load *,
.....;
Drop Table RQMS_STAMM;
Regards,
Antonio
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
Amit,
try with Preceding Load.
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
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
];
Tresesco,
I already did it ,as you can see in script in my post:
num(Month(DTRQDATUM)) as RQMS_Month
Thanks,
AS
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
;