Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

How to create Month name column while loading data from database

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";

1 Solution

Accepted Solutions
kedar_dandekar
Contributor

Re: How to create Month name column while loading data from database

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

View solution in original post

4 Replies
kedar_dandekar
Contributor

Re: How to create Month name column while loading data from database

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

Not applicable

Re: How to create Month name column while loading data from database

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
Contributor

Re: How to create Month name column while loading data from database

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

View solution in original post

Not applicable

Re: How to create Month name column while loading data from database

Thanks a lot KD, it works.