Getting Date Part from specific text format

[#1]

Hi,

I have dimension that contains values as below text format.Is there logic to get date part from the below text.

abc.def_APR_07_2017_11H_18m_47s

I need to convert APR_07_2017_11H_18m_47s to date format.Is it possible to do in qliksense

[arulsettu]

may be like this

=date(Date#(Left(right('abc.def_APR_07_2017_11H_18m_47s',23),11),'MMM_DD_YYYY'),'DD/MM/YYYY') as date

replace this --- 'abc.def_APR_07_2017_11H_18m_47s' with your dimension

[viveksingh]

does your text have same kind of prefix?

like below

abc.def_APR_07_2017_11H_18m_47s

the texy is having same prefix like abc.def_ 

or it will change?

Please post some more sample data

[OmarBenSalem]

Sure:

try sthing like this:

load timestamp(Timestamp#(time,'MMMDDYYYYhhmmss')) as timestampField,

Date(Timestamp#(time,'MMMDDYYYYhhmmss')) as DateField;

load replace(replace(replace(replace(replace(time,subfield(time,'_',1),''),'_',''),'H',''),'m',''),'s','') as time;

load * Inline [

time

abc.def_APR_07_2017_11H_18m_47s

];

just replace time by your field name and add that code over your table contaiing the field you want to convert

result:

[jonathandienst]

Assuming that the dates always have the same subfields delimited by underscores - this will pull the date:

=MakeDate(SubField(datefield, '_', 4), Month(Date#(SubField(datefield, '_', 2), 'MMM')), SubField(datefield, '_', 3))

[#6]

There is change in prefix, sometimes there will be more characters, and sometimes very few. This is how it exactly looks.

abc.defg_hij_klmn_APR_07_2017_11H_18m_47s.aaa

abc.defg_hiasj_klanmn_APR_07_2017_11H_18m_47s.bbb

abc.defg_hijgfdo_ktyulmn_APR_07_2017_11H_18m_47s.ttt

[arulsettu]

jonathan answer will work for your data

try this

MakeDate(SubField(dates, '_', 6), Month(Date#(SubField(dates, '_', 4), 'MMM')), SubField(dates, '_', 5)) as date

[OmarBenSalem]

This would also work:

It will create a date field, a time field and a timestamp field no matter how long is your prefix :

load timestamp(Timestamp#(time,'MMMDDYYYYhhmmss')) as timestampField,

Date(Timestamp#(time,'MMMDDYYYYhhmmss')) as DateField,

time(Timestamp#(time,'MMMDDYYYYhhmmss')) as Timefield;

load SubField(replace(replace(replace(replace( right(time,27),'_',''),'H',''),'m',''),'s',''),'.',1) as time;

load * Inline [

time

abc.defg_hij_klmn_APR_07_2017_11H_18m_47s.aaa

abc.defg_hiasj_klanmn_APR_07_2017_11H_18m_47s.bbb

abc.defg_hijgfdo_ktyulmn_APR_07_2017_11H_18m_47s.ttt

];

result:

[#9]

Hi Omar

This solution is working for the below format

abc.defg_hij_klmn_APR_07_2017_11H_18m_47s.aaa

However If I have something like below i.e.,instead of 08 in date part,i have 8 in some rows,then it is showing differently

abc.defg_hiasj_klanmn_APR_8_2017_11H_18m_47s.bbb

abc.defg_hijgfdo_ktyulmn_APR_1_2017_11H_18m_47s.ttt

Below is the Result

[OmarBenSalem]

In that case, here's how I did:

load timestamp(Timestamp#(time,'MMMDDYYYYhhmmss'))& timestamp(Timestamp#(time,'MMMDYYYYhhmmss'))as timestampField,

Date(Timestamp#(time,'MMMDDYYYYhhmmss'))&Date(Timestamp#(time,'MMMDYYYYhhmmss')) as DateField,

time(Timestamp#(time,'MMMDDYYYYhhmmss'))&time(Timestamp#(time,'MMMDYYYYhhmmss')) as Timefield;

load SubField(replace(replace(replace

(replace(right(time,27),'_',''),'H',''),'m',''),'s',''),'.',1) as time;

load * Inline [

time

abc.defg_hij_klmn_APR_07_2017_11H_18m_47s.aaa

abc.defg_hiasj_klanmn_APR_8_2017_11H_18m_47s.bbb

abc.defg_hiasj_klanmn_APR_07_2017_11H_18m_47s.bbb

abc.defg_hijgfdo_ktyulmn_APR_07_2017_11H_18m_47s.ttt

];

result: