Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guru's
how to get this field(FKDAT) information as a multiple fields in EDIT SCRIPT from VBRK table?
exp:
Field is FKDAT from VBRK table
need split informtion like this,
Invoice Month,
Invoice Date,
Invoice Year,
Invoice Quarter,
Invoice Day,
Invoice Week,
Invoice WeekDay,
Invoice Monthyear,
YearMonth,
LogicalMonthYear
all those fields i need to get only "FKDAT from VBRK table"
pls let me kow any one
rgds
VBRK_BillingDocument_Header:
Load
Date(FKDAT,'MMddyyyy') AS [Invoice Date_FKDAT],
Date(FKDAT,'MM') AS [Invoice Month_FKDAT],
Date(FKDAT,'yyyy') AS [Invoice Year_FKDAT],
KUNRG AS [Payer_KUNRG],
//FKDAT AS [Billing Date_FKDAT],
//WAERK AS [Document Currency_WAERK],
VBELN AS [VBELN],
FKART AS [Billing Type_FKART],
VKORG AS [Billing Sales Org._VKORG],
BUKRS AS [Compnay Code._BUKRS],
WAERK AS [Billing Doc.currency_WAERK],
VTWEG AS [Billing Distribution Channel_VTWEG];
SQL SELECT FKDAT KUNRG VBELN FKART VKORG BUKRS WAERK VTWEG FROM VBRK;
rgds
Shiva
HI,
Try this way.
Load FKDAT,
Month(FKDATE) as Month,
Date(FKDATE) as Date,
Year(FKDATE) as Year,
Day(FKDATE) as Day,
week(FKDATE) as Week,
WeekDay(FKDATE) as WeekDay,
MonthName(FKDATE) as MonthYear
From VBRK
Regards,
Kaushik Solanki
Hi,
The best way is loading this field as a date and link to a calendar table, where you have all the date related dimensions. This example should work:
Data:
LOAD *,
Date(FKDAT) AS DateField;
SQL SELECT * FROM VBRK;
DatesMinMax:
LOAD Max(DateField) AS DateMax,
Min(DateField) AS DateMin
RESIDENT Data; // you can do the min and max in the SQL and will perform faster likely
LET vMinDate = FieldValue('DateMin', 1);
LET vMaxDate = FieldValue('DateMax', 1);
DROP TABLE DatesMinMax;
Calendar:
LOAD Date($(vMinDate) + RowNo() -1) AS DateField,
Month($(vMinDate)+ RowNo() -1) AS DateMonth,
Year($(vMinDate)+ RowNo() -1) AS DateYear,
... // any other dimensions
AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;
The result is two tables, one with the data the other with the date dimensions related to the main table. Should you need to add any other table to the fact table, the calendar will work as well.
Hope that helps.
BI Consultant
VBRK_BillingDocument_Header:
Load
Date(FKDAT,'MMddyyyy') AS [Invoice Date_FKDAT],
Date(FKDAT,'MM') AS [Invoice Month_FKDAT],
Date(FKDAT,'yyyy') AS [Invoice Year_FKDAT],
KUNRG AS [Payer_KUNRG],
//FKDAT AS [Billing Date_FKDAT],
//WAERK AS [Document Currency_WAERK],
VBELN AS [VBELN],
FKART AS [Billing Type_FKART],
VKORG AS [Billing Sales Org._VKORG],
BUKRS AS [Compnay Code._BUKRS],
WAERK AS [Billing Doc.currency_WAERK],
VTWEG AS [Billing Distribution Channel_VTWEG];
SQL SELECT FKDAT KUNRG VBELN FKART VKORG BUKRS WAERK VTWEG FROM VBRK;
rgds
Shiva