Script error

[nareshthavidishetty]

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

[sunny_talwar]

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?

[marcus_sommer]

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

[nareshthavidishetty]

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

[sunny_talwar]

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

[nareshthavidishetty]

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,

[nareshthavidishetty]

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