Identifying month date year from given field.

[deepakqlikview_123]

Hello team,

Please find attached QVW.

Can u plz suggest how to indentify year,month,date from column title.

Thanks,

[Jason_Michaelides]

Deepak,

All of the above suggestions can be used, however none of them will work on all your records as your data is a mess!!  You have a real mix of date formats in there e.g.

d mmmm yyyy

d  mmmm yyyy (two spaces after d)

dd mmm yyyy

dd mmmm yyyy

So you need to clean up your data first, really so you can convert these random strings into date fields.  You could automate this in QV using a mapping table of every format you can think of I guess...

Hope this helps,

Jason

[Gysbert_Wassenaar]

Table:
LOAD *, year(Date) as year, month(Date) as month, week(Date) as week, day(Date) as Day;
LOAD *, date#(replace(Title,'  ',' '),'DD MMMM YYYY') as Date;
LOAD Title, 
     [CDMS AUDIT LOG], 
     [CDMS WSS CONTENT], 
     [CDMSLive DATABASE], 
     [LOG size CDMS AUDIT LOG], 
     [LOG size CDMS WSS CONTENT], 
     [LOG size CDMSLive Database], 
     [CDMS AUDITLOG VESPA], 
     [CDMSLIVE_VESPA DATABASE], 
     [LOG size CDMS AUDITLOG VESPA], 
     [LOG size CDMSLIVE_VESPA Database]
FROM

(ooxml, embedded labels, table is [CDMS & Vespa Values in Column]);

See attached qvw.

[Anonymous]

Hi,

Below script should work with all above mentioned mess up data.

Date(Date#(Num(Trim(Left(Title, 2)), 00)&Left(Trim(Mid(Title, 3, Len(Title)-6)),3)&Trim(Right(Title, 4)), 'DDMMMYYYY')) as Date

Thanks

[deepakqlikview_123]

Hello Expert,

can you please suggest how to get the day.

It is not showing perfectly.

Thanks

[deepakqlikview_123]

Hi Anand,

Not able to retrieve day.Can you please suggest.

Thanks

[deepakqlikview_123]

Hi Jason,

Thanks for reply,Can you please suggest on this.

Currently i am not able to make the formatting as per requirement.

Thanks

[its_anandrjs · Accepted solution]

It is working perfect from my side also you can try with this

LOAD Title,

     Day(Date#(Left(Title,2),'DD')) as Day,

     Month(Date#(Title,'DD MMMM YYYY')) as Month,

     Year(Date#(Right(Title,4),'YYYY')) as Year,

     [CDMS AUDIT LOG],

     [CDMS WSS CONTENT],

     [CDMSLive DATABASE],

     [LOG size CDMS AUDIT LOG],

     [LOG size CDMS WSS CONTENT],

     [LOG size CDMSLive Database],

     [CDMS AUDITLOG VESPA],

     [CDMSLIVE_VESPA DATABASE],

     [LOG size CDMS AUDITLOG VESPA],

     [LOG size CDMSLIVE_VESPA Database]

FROM

(ooxml, embedded labels, table is [CDMS & Vespa Values in Column]);

[deepakqlikview_123]

hi anand,

For some values of month it is not working for eg dec 2012 please suggest.

Thanks

[Gysbert_Wassenaar]

You need to remove the double spaces from those Title values. See my post above.

[its_anandrjs]

For only Month field try below scripts it is due to space in the title field for the December 2012 values see the MOnth fields for that and change according and use one of them

Option One

Month(Date#(Trim(PurgeChar(Title,0123456789)),'MMMM')) as Month,

Option Two

Month(Date#(Replace(Title,'  ',' '),'DD MMMM YYYY')) as Month2,