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

How to create Month name column while loading data from database

Sat, 07 Sep 2013 17:55:24 GMT

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

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

kedar_dandekar — Sat, 07 Sep 2013 19:29:59 GMT

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,

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

Sat, 07 Sep 2013 19:41:46 GMT

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"

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

kedar_dandekar — Sat, 07 Sep 2013 19:51:27 GMT

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

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

Sat, 07 Sep 2013 19:57:32 GMT

Thanks a lot KD, it works.