topic Re: Build Fiscal year while loading from a QVD in QlikView

Build Fiscal year while loading from a QVD

Fri, 06 Apr 2012 13:32:20 GMT

HI guys,

I am loading data from a qvd and I have a date field in there.

how can I build a Fiscal year that starts on July?

I used to do that in SQL in the script using the follwoing method:

CASE

WHEN Month(P.DATE) = 1 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 2 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 3 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 4 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 5 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 6 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 7 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 8 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 9 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 10 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 11 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 12 THEN YEAR(P.DATE)+1

ELSE

YEAR(P.DATE)

END AS SALESYEAR

Now I have no idea how to do it while loading from a QVD

Wish you can help!

Thxs,

Build Fiscal year while loading from a QVD

jagannalla — Fri, 06 Apr 2012 14:07:48 GMT

Hello,

Load *,

If(Month(DateField)*1=7,Year(DateField)) as JulyMnthYr

from

table.qvd;

Hope it helps you.

Build Fiscal year while loading from a QVD

Fri, 06 Apr 2012 14:15:27 GMT

Hmm!..

I dont think that would help.

What I am looking for it build the fY Year in a simillar manar to what I have on the SQl query above.

WHEN Month(P.DATE) = 1 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 2 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 3 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 4 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 5 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 6 THEN YEAR(P.DATE)

WHEN Month(P.DATE) = 7 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 8 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 9 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 10 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 11 THEN YEAR(P.DATE)+1

WHEN Month(P.DATE) = 12 THEN YEAR(P.DATE)+1

Thxs

Build Fiscal year while loading from a QVD

jagannalla — Fri, 06 Apr 2012 14:34:12 GMT

u r implementing the logic to get year for particualar months. But what you want i didn't get. Can you explain little bit with example.

I mean wt is the input of ur qvd file and wt is the output u want.

Build Fiscal year while loading from a QVD

Fri, 06 Apr 2012 14:39:09 GMT

On this example

WHEN Month(P.DATE) = 1 THEN YEAR(P.DATE) //returns Current Year

WHEN Month(P.DATE) = 2 THEN YEAR(P.DATE) //return Current Year

WHEN Month(P.DATE) = 3 THEN YEAR(P.DATE) //return Current Year

WHEN Month(P.DATE) = 4 THEN YEAR(P.DATE) //return Current Year

WHEN Month(P.DATE) = 5 THEN YEAR(P.DATE) // return Current Year

WHEN Month(P.DATE) = 6 THEN YEAR(P.DATE) //return Current Year

WHEN Month(P.DATE) = 7 THEN YEAR(P.DATE)+1 //return Next Year

WHEN Month(P.DATE) = 8 THEN YEAR(P.DATE)+1 //return Next Year

WHEN Month(P.DATE) = 9 THEN YEAR(P.DATE)+1 //return Next Year

WHEN Month(P.DATE) = 10 THEN YEAR(P.DATE)+1 //Next 2012

WHEN Month(P.DATE) = 11 THEN YEAR(P.DATE)+1 //Next 2012

WHEN Month(P.DATE) = 12 THEN YEAR(P.DATE)+1 //Next 2012

so that will make the Year start on July

On your example

If(Month(DateField)*1=7,Year(DateField)) as JulyMnthYr

How can I add Else IF ?

Thxs for your help

Re: Build Fiscal year while loading from a QVD

v_iyyappan — Fri, 06 Apr 2012 14:42:52 GMT

Hi,

use the code in script like this

Load

If((Month(Date)*1=1),Year(Date),

If((Month(Date)*1=2),Year(Date),

If((Month(Date)*1=3),Year(Date),

If((Month(Date)*1=4),Year(Date),

If((Month(Date)*1=5),Year(Date),

If((Month(Date)*1=6),Year(Date),

If((Month(Date)*1=7),Year(Date)+1,

If((Month(Date)*1=8),Year(Date)+1,

If((Month(Date)*1=9),Year(Date)+1,

If((Month(Date)*1=10),Year(Date)+1,

If((Month(Date)*1=11),Year(Date)+1,

If((Month(Date)*1=12),Year(Date)+1,Year(Date))))))))))))) AS SALESYEAR

From DataSource;

Where Date = P.DATE

Regards,

Iyyappan

Build Fiscal year while loading from a QVD

Fri, 06 Apr 2012 15:03:54 GMT

Thxs! this worked perfect!

Build Fiscal year while loading from a QVD

jagan — Fri, 06 Apr 2012 15:20:12 GMT

Hi,

Fine tuned script.

Table3:

Load

Month(Date) AS Month,

If(Month(Date) > 6, Year(Date) +1, Year(Date)) AS YearMonth

Resident Table2;

Regards,

Jagan.

Build Fiscal year while loading from a QVD

CELAMBARASAN — Fri, 06 Apr 2012 17:03:16 GMT

Hi,

Check with this

Load

Month(Date) AS Month,

Year(AddMonths(Date,6)) AS SalesYear

Resident Table2;

Celambarasan