i need to get multiple fields from single field(FKDAT) of table VBRK?How we can get all those?

[#1]

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

[kaushiknsolanki]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4 · Accepted solution]

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