topic Re: Script error in QlikView

Script error

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Am getting the below error while am ran the script,

Below is the script,

MinMaxs:

LOAD Min(racApp_Date) as MinDates,

Max(racApp_Date) as MaxDates

Resident RACAPPLI;

SET vFiscalYearStartMonths = 2;

LET vStartDates = Peek('MinDates');

LET vEndDates = Peek('MaxDates');

DROP Table MinMaxs;

racFiscalCalendar:

LOAD *,

Dual('Q' & Ceil(racFIMonth/3), Ceil(racFIMonth/3)) AS racApp_Quarter,

Dual(Text(Date(MonthEnd(racApp_Date), 'MMM')), racFIMonth) AS racApp_Month;

LOAD *,

Year(racApp_Date) AS racYears, // Standard Calendar Year

Month(racApp_Date) AS racMonths,

day(racApp_Date) as racApp_Day,

Date(MonthEnd(racApp_Date), 'MMM') AS racMonth,

Dual('Q' & Ceil(Month(racApp_Date)/3), Ceil(Month(racApp_Date)/3)) AS racQuarter,

Mod(Month(racApp_Date) - $(vFiscalYearStartMonths), 12)+1 AS racFIMonth,

YearName(racApp_Date, 0, $(vFiscalYearStartMonths)) AS racFIYear;

LOAD Date($(vStartDates) + RangeSum(Peek('RowNums'), 1) - 1) AS racApp_Date,

RangeSum(Peek('RowNums'), 1) AS RowNums

AutoGenerate vEndDates - vStartDates + 1;

Thanks...

Re: Script error

sunny_talwar — Thu, 14 Apr 2016 14:31:28 GMT

I think you need to check your racApp_Date field. This might not be read as date field and you will need to help QlikView to recognize it as Date using Date#() function.

Get the Dates Right

Why don’t my dates work?

Re: Script error

marcus_sommer — Thu, 14 Apr 2016 14:32:06 GMT

Your variables are empty - if you used peek() outside from a load you need to specify the table from where the data should come from. Try:

LET vStartDates = Peek('MinDates', 0, 'MinMaxs');

- Marcus

Re: Script error

nareshthavidishetty — Thu, 14 Apr 2016 14:46:02 GMT

Hi below is the script to define racApp_Date field,

APP:

LOAD

date((FCFSDT),'MM/D/YYYY') as SUB_Date,

FCFSDT,

COMP,

FCAACD as Location,

APPL# as ApplicationNbr,

FCALNB as CustomerID,

[FCK0ST],

pick(match([FCK1ST],'R','W'),'In-store','Offsite Site (Web+Ph+Mail)') as Channel,

pick(match([FCK0ST],'A','C','D','I','Q','U','W'),'Approved','Canceled','Declined','In Store','queued','Used','Waiting') as App_Status,

pick(match([COMP],'C3D6D5','C7C5D4'),'Conns Application','GE Application') as App_Category,

Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY') as App_Date

SQL SELECT *

FROM AS400D60.SPCTDBF.APPLOCCNT where FCFSDT>='1140101';

RACAPPLI:

LOAD ApplicationNbr as racApplicationNbr,

[COMP] as raccomp,

App_Category as racAppCategory,

Location as racLocation,

CustomerID as racCustomerID,

FCFSDT as racFCFSDT,

App_Status as racAppStatus,

Channel as racChannel,

LocationDesc as rsalocdes,

App_Date as racApp_Date,

State as RACState,

Zipcode as RACZipCode,

City as RACCity

RESIDENT APP

WHERE App_Status='Declined'and App_Category='Cos Application' ;

Thanks..

Re: Script error

sunny_talwar — Thu, 14 Apr 2016 14:56:39 GMT

Can you create a chart in your application with racApp_Date as dimension and Num(racApp_Date) as your expression. If you see all nulls then you date is not read correctly. It is difficult to understand this logic without having details about FCFSDT field

Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY') as App_Date

Re: Script error

nareshthavidishetty — Thu, 14 Apr 2016 15:15:59 GMT

Hi Sunny,

The racApp_Date is showing null but App_Date is having dates and racApp_Date is resident of App_Date.

Why it is showing blank.

FCFSDT field has date formats as below,

Re: Script error

nareshthavidishetty — Thu, 14 Apr 2016 15:51:52 GMT

Hi,

City as RACCity

RESIDENT APP

WHERE App_Status='Declined'and App_Category='Cos Application' ;

The App_Category field values were renamed in backend and this is the issue happend.

Thanks..a lot for you time and support