Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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:
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))
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
jonathan answer will work for your data
try this
MakeDate(SubField(dates, '_', 6), Month(Date#(SubField(dates, '_', 4), 'MMM')), SubField(dates, '_', 5)) as date
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:
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
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: