How to create Month name column while loading data from database

[#1]

The following script create month number, i want month name in 3 character, i.e. JAN,FEB etc

CCMS:

SQL SELECT

ProjectTypeName,

Vertical,

CustomerId,

Customer,

Inv_Date,

Inv_Amt_Qr as Inv_Amount,

Month(Inv_date)as Month,

Year(Inv_date) as Year,

FROM CCMS.dbo."VW_PROJECT_CLIENT_INVOICES";

[kedar_dandekar]

Hi Jayadevan,

Please use the Preceding Load command and carry out the transformations at QlikView side.

In this case, the database transforms can be moved to QlikView Load statement as given below:

CCMS:

LOAD

    ProjectTypeName,

    Vertical,

    CustomerId,

    Customer,

    Inv_Date,

    Inv_Amt_Qr as Inv_Amount,

    Month(Inv_date)as Month, //Jan, Feb

    Num(Month(Inv_date) as Month_Num //1, 2

    Year(Inv_date) as Year;

SQL SELECT

    ProjectTypeName,

    Vertical,

    CustomerId,

    Customer,

    Inv_Date,

    Inv_Amt_Qr

FROM CCMS.dbo."VW_PROJECT_CLIENT_INVOICES";

This should do the trick..

HTH,

KD

[#3]

Thanks KD, however it shows the following error while execution.  Inv_date is a column available in the table.

Field not found - <Inv_date>

SQL SELECT

    ProjectTypeName,

    Vertical,

    CustomerId,

    Customer,

    Inv_Date,

    Inv_Amt_Qr

FROM CCMS.dbo."VW_PROJECT_CLIENT_INVOICES"

[kedar_dandekar · Accepted solution]

Hi Jayadevan,

Sorry, my bad.

I think probably the problem is that QlikView being Case Sensitive with table and column names, is unable to find the column, can you please check this aspect and try again..

Check if this works:

CCMS:

LOAD

    ProjectTypeName,

    Vertical,

    CustomerId,

    Customer,

    Inv_date,

    Inv_Amt_Qr as Inv_Amount,

    Month(Inv_date)as Month, //Jan, Feb

    Num(Month(Inv_date) as Month_Num //1, 2

    Year(Inv_date) as Year;

SQL SELECT

    ProjectTypeName,

    Vertical,

    CustomerId,

    Customer,

    Inv_date,

    Inv_Amt_Qr

FROM CCMS.dbo."VW_PROJECT_CLIENT_INVOICES";

- KD

[#5]

Thanks a lot KD, it works.