topic Re: Prob in understanding the below script !! in QlikView

Prob in understanding the below script !!

nikhilgarg — Tue, 06 Jan 2015 09:36:09 GMT

Hey,

I didn't get some of the statements in below script:

1) What does Dual('Q' & Ceil(FiscalMonth/3) return ?? And is value for FiscalMonth = 12 ??

2) What does Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) return ?? and int he functionMonthEnd(Date) does Date is the current date ??

3) What does Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date returns ?? and What is the value of'RowNum' ??

Can you explain please ??

Thanks

Script:

----------

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

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

AutoGenerate vEndDate - vStartDate + 1;

Re: Prob in understanding the below script !!

jagan — Tue, 06 Jan 2015 09:46:04 GMT

1) What does Dual('Q' & Ceil(FiscalMonth/3) return ?? And is value for FiscalMonth = 12 ?? - This is to arrive the Quarter column with numeric value using Dual, the second parameter is a number, while sorting this is very useful, if we select Numeric in Sort tab it will sort accordingly.

2) What does Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) return ?? and int he functionMonthEnd(Date) does Date is the current date ?? - Date is the date field in the table, here the date is returned as a text in MMM format, and used dual function for sorting purpose.

3) What does Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date returns ?? and What is the value of'RowNum' ??

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) -- Here we are generating the dates vStartDate will have the starting calendar date and Peek will return the value of the previous record.

Hope this helps you.

Regards,

Jagan.

Re: Prob in understanding the below script !!

Peter_Cammaert — Tue, 06 Jan 2015 09:48:22 GMT

Dual() returns a field value that has two identities, a string version (for display) and a numerical version (for caluclation). FiscalMonth contains the month number in the fiscal year. For example, if your FiscalYear starts on April 1st, then for May FiscalMonth will be 2.
Returns a dual value with a regular month name and a FiscalMonth value. E;g. ('May', 2) in my previous example. Date is a generated date value. See the first (bottom) LOAD statement from the PRECEDING LOAD.
RowNum contains the current row number. Peek('RowNum') returns the row number of the previous row we processed. This is an overly complex way to generate increasing numbers. Better do this with RowNo().

Peter