topic How to load the financial year during the load script itself? in QlikView

How to load the financial year during the load script itself?

Sat, 13 Mar 2010 08:52:21 GMT

Hi All,

How to load the financial year during the load script in the same way which has been given below????


Calender:
 Load
 DATE,
 Day(DATE) as Day,
 DayName(DATE) as [Day Name],
 WeekDay(DATE) as [Day of Week],
 WeekName(DATE) as [Week Name],
 LunarWeekName(DATE) as [Lunar Week Name],
 Month(DATE) as [Month],
 MonthName(DATE) as [Month Name],
 QuarterName(DATE) as [Quarter Name],
 Year(DATE) as [Year]
 ????(DATE) as [FinancialYear]

Resident t1;

Drop Table t1;

How to load the financial year during the load script itself?

Sat, 13 Mar 2010 17:24:02 GMT

you can use this

YearName(DATE,0,4) as [FinancialYear]

eg.

YearName(Today(),0,4) it will give 2009-2010

Rahul

How to load the financial year during the load script itself?

Oleg_Troyansky — Sun, 14 Mar 2010 02:24:35 GMT

We usually load the table of fiscal periods with the beginning and end dates, and then generate the calendar for all the days the belong to each period, using WHILE. This way, each date is getting associated with the fiscal Year and Period number.

How to load the financial year during the load script itself?

Anonymous — Mon, 15 Mar 2010 08:20:41 GMT

HI, dsjain,

If my memory doesn't fail, then the begining and the end of the financial year could be different from calendar year. I have similar case with quarters what are different from quarters based on months. I use the source table with period ending dates and Lookup function.

In your case may be source table in Excel and Intervalmatch statement could be the solution

E.g. the source table could look like...

Finansial year Begin Date End Date
2006 2006.02.01 2007.01.31
2007 2007.02.01 2008.01.31
2008 2008.02.01 2009.01.31
2009 2009.02.01 2010.01.31
2010 2010.02.01 2011.01.31

...and the statement


FinYears:
Load
 [Financial Year] as FinYear,
 Num(Date([Begin Date])) as FinBegin,
 Num(Date([End Date])) as FinEnd
From SourceExcel.xls........;

Left Join (Calender)
Intervalmatch (DATE, FinYear)
Load
 FinBegin,
 FinEnd,
 FinYear
Resident FinYears;

After that you can join table FinYears to Calender and drop unnecessary table and fields.

That just one of the possible solutions

Rds,
AT

How to load the financial year during the load script itself?

Mon, 15 Mar 2010 09:01:31 GMT

If you are looking for a function, like all the other functions in you example, that returns the FinancialYear.. I'm afraid you won't find it.

A FinancialYear does not have a standard function, because there is no default definition for it. The definition for FinancialYear is something you will have to make yourself, because it can be different for each company.

Your Financial Year could start on February 2nd, or October 21st, or any other date..

Three possible options: (there are more)

- If your Financial year is the same as the Calendar Year, you can just use Year()

- If it is not too complex, you might think of a calculation, for example: if your financial year begins in March, then you could do something like


if( Month(DATE)<=3,
  Year(DATE) -1,
  Year(DATE),
) as [FinancialYear]

- If the definition is complex, you should make a seperate table (import or inline) that you can then join/map to you existing calender, like already suggested by Artjoms Tukums

How to load the financial year during the load script itself?

Mon, 15 Mar 2010 10:50:14 GMT

Hi Rahul!

Thanks for your reply! Instead of using the script sent by you I am using another script and getting the required result. May i know will it affects some time later on.

YearName(DATE,0,4) as [FinancialYear1]

// Instead of using the above one. I am using the below one

Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year]

Thanks and Regards,

Rikab

How to load the financial year during the load script itself?

Mon, 15 Mar 2010 11:19:55 GMT

Hi,

Both will give the same result

only difference is in out put

YearName(DATE,0,4) will give 2009-2010

Date(yearstart(DATE,0,4),'YYYY') will give 2009

other wise both are correct for any given date

Rahul

How to load the financial year during the load script itself?

Wed, 17 Mar 2010 06:02:19 GMT

Hi At,

Using the below function I am getting the required result. Please check and let me know whether I am correct or not. If I am wrong let me know where I am wrong.


Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year] // Gives output as 2005,2006
YearName(DATE,0,4) as [FinancialYear1] // Gives output as 2005-2006,2006-2007

Thanks and Regards,

Rikab

How to load the financial year during the load script itself?

Wed, 17 Mar 2010 06:06:08 GMT

Hi MdBoer,

I got it done by using the below function. As for your clients financial year starts from april 1st and not in the middle like that way you have mentioned in your example. Please refer and put me your suggestions.

<pre>Date(yearstart(DATE,0,4),'YYYY') as [Fiscal Year] // Gives output as 2005,2006
YearName(DATE,0,4) as [FinancialYear1] // Gives output as 2005-2006,2006-2007

Thanks and Regards,

Rikab