Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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


1 Solution

Accepted Solutions
Not applicable

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

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

3 Replies

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

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

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

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

Not applicable

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

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