Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cworkman2015
New Contributor III

Function in Script returns different value than function in Text object

Hello,

I'm experiencing something very confusing. I am loading a temporary table with this script:

Temp_Calendar_Range:

Load

Num(Date#(Min([Accrual Period]), 'YYYYMM'))  as MinDate,

Num(Date#(Max([Accrual Period]), 'YYYYMM'))  as MaxDate

Resident [Fact_Accrual];

//Assign the start and end dates to variables

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

Let vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

However, the MinDate and MaxDate values from the script do not match this same function in a text object.

The table on the right is the Temp_Calendar_Range table. The Blue box is the text object. Note that MinDate for Temp table and the Blue box are completely different.

dateissue.PNG

Has anyone experienced this before?

Thanks,

Channing

2 Replies

Re: Function in Script returns different value than function in Text object

May be try to convert the Date first, then do the minimum / Maximum.. Like

Temp_Calendar_Range:

Load

Num(Min(Date#([Accrual Period], 'YYYYMM')))  as MinDate,

Num(Max(Date#([Accrual Period], 'YYYYMM')))  as MaxDate

Resident [Fact_Accrual];

Can you tell what is the format of Accrual Period / may be some sample?

cworkman2015
New Contributor III

Re: Function in Script returns different value than function in Text object

Settu,

That worked. However, now I'm seeing Accrual Periods that appear to be interpreted incorrectly.

Before Calendar Creation

Accrual Period Range: 201601 - 201712 (Date Format YYYYMM)

After Calendar Creation

Accrual Period Range: 201601 - 245204 (Date Format YYYYMM)

I have no clue where these values are coming from.. Maybe the max period is being interpreted  incorrectly. Here's my script.

//////Accrual Fact Table

Temp_Calendar_Range:

Load

Num(Min(Date#([Accrual Period], 'YYYYMM')))   as MinDate,

Num(Max(Date#([Accrual Period], 'YYYYMM')))  as MaxDate

Resident [Fact_Accrual];

//Assign the start and end dates to variables

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

Let vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

Drop table Temp_Calendar_Range; //cleanup

[Master Calendar Fact Accrual]:

Load Distinct

Year(Temp_Date) * 100 + Month(Temp_Date) as [Accrual Period],

Year(Temp_Date) as [Accrual Year],

Month(Temp_Date) as [Accrual Month],

Date(Temp_Date, 'YYYY-MM') as [Accrual Year - Month],

'Q' & Ceil(Month(Temp_Date) / 3) as [Accrual Quarter]

;

Load Distinct

MonthStart($(vMinDate) + IterNo()-1) as Temp_Date

AutoGenerate(1)

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//Remove the temporary values

LET vMinDate = null();

LET vMaxDate = null();