topic Re: Script Help ???? in QlikView

Script Help ????

amit_saini — Wed, 16 Jul 2014 09:37:36 GMT

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,

Re: Script Help ????

antoniotiman — Wed, 16 Jul 2014 09:53:51 GMT

Hi Amit,

You must use NoConcatenate for Table PPMDeliveries..

PPMDeliveriesExport:

NoConcatenate

Load *,

.....;

Drop Table RQMS_STAMM;

Regards,

Antonio

Re: Script Help ????

amit_saini — Wed, 16 Jul 2014 10:17:56 GMT

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 ????

antoniotiman — Wed, 16 Jul 2014 10:22:57 GMT

Amit,

try with Preceding Load.

Re: Script Help ????

tresB — Wed, 16 Jul 2014 10:24:27 GMT

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 ????

its_anandrjs — Wed, 16 Jul 2014 10:31:09 GMT

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

];

Re: Script Help ????

amit_saini — Wed, 16 Jul 2014 10:31:29 GMT

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 ????

tresB — Wed, 16 Jul 2014 10:39:06 GMT

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

;