topic Re: time dimension require in QlikView

time dimension require

yadav_anil782 — Tue, 25 Aug 2015 05:25:09 GMT

Dear Comunity ,

because of "Mar_Adj-16" date formate in given table i'm not able to create time dimension like year , month, quarter . anyone can suggest how i can create time dimention form this column

PERIOD_NAME

Apr-16

Aug-15

Dec-15

Feb-16

Jan-16

Jul-15

Jun-15

Mar-16

Mar_Adj-16

Nov-15

Oct-15

Sep-15

May-15

Apr-15

Re: time dimension require

Digvijay_Singh — Tue, 25 Aug 2015 05:32:35 GMT

You want to skip this date or to consider it as Mar-16, if it is to be considered as Mar-16 then you will have duplicate record it seems. Was thinking if you can purgechar before loading if this is the only record and text you need to deal with.

Re: time dimension require

sasiparupudi1 — Tue, 25 Aug 2015 05:52:10 GMT

try like this

temp:

LOAD Month(PERIOD_NAME) as PERIOD_NAME_MONTH,Year(PERIOD_NAME) as PERIOD_NAME_YEAR,PERIOD_NAME;

LOAD date#(replace(PERIOD_NAME,'_Adj',''),'MMM-YY') as PERIOD_NAME

Inline

[

PERIOD_NAME

Apr-16

Aug-15

Dec-15

Feb-16

Jan-16

Jul-15

Jun-15

Mar-16

Mar_Adj-16

Nov-15

Oct-15

Sep-15

May-15

Apr-15

];

Re: time dimension require

Kushal_Chawda — Tue, 25 Aug 2015 06:20:25 GMT

Use mapping load table to include the Patterns

Map:

mapping LOAD * Inline [

PERIOD_NAME,Month

Mar_Adj-16,Mar-16 ];

Data:

LOAD *,

year(date#(PERIOD_NAME_NEW,'MMM-YY')) as YEAR,

MONTH(date#(PERIOD_NAME_NEW,'MMM-YY')) as MONTH,

MONTHNAME(date#(PERIOD_NAME_NEW,'MMM-YY')) as MONTH_YEAR;

LOAD ACTUAL,

BUDGET,

BUDGET_NAME,

BUDGET_ORGANIZATION,

CODE_COMBINATION_ID,

PERIOD_NAME,

ApplyMap('Map',PERIOD_NAME) as PERIOD_NAME_NEW,

PERIOD_YEAR,

SEGMENT4,

SEGMENT5,

PROJECT_CODE

FROM

[need to be date create.xls]

(biff, embedded labels, table is [Sheet1$]);

See the attached

Re: time dimension require

Anonymous — Tue, 25 Aug 2015 06:35:16 GMT

Hi why don't you use replace(PERIOD_NAME,'_Adj','') this will remove the _Adj in all of the strings allowing you to use the normal Qlik functions to create the dates you require ?