Build Fiscal year while loading from a QVD

[#1]

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,

[jagannalla]

Hello,

Load *,

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

from

table.qvd;

Hope it helps you.

[#3]

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

[jagannalla]

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.

[#5]

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

[v_iyyappan · Accepted solution]

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

[#7]

Thxs! this worked perfect!

[jagan]

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.

[CELAMBARASAN]

Hi,

     Check with this

Load

    Month(Date) AS Month,

   Year(AddMonths(Date,6)) AS SalesYear

Resident Table2;

Celambarasan