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

Function in Script returns different value than function in Text object

Anonymous — Mon, 17 Jul 2017 19:56:20 GMT

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.

Has anyone experienced this before?

Thanks,

Channing

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

settu_periasamy — Tue, 18 Jul 2017 06:31:47 GMT

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?

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

Anonymous — Tue, 18 Jul 2017 13:49:16 GMT

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();