Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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";
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
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
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"
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
Thanks a lot KD, it works.