topic Re: Identifying month date year from given field. in QlikView

Identifying month date year from given field.

deepakqlikview_123 — Thu, 12 Jun 2014 08:27:36 GMT

Hello team,

Please find attached QVW.

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

Thanks,

Re: Identifying month date year from given field.

Anonymous — Thu, 12 Jun 2014 08:35:27 GMT

Here you go,

If you want to create a separate List boxes create the expressions like below

Year

right(Title,4)

Month:

TextBetween(Title,' ',' ')

Date:

TextBetween(Title,'',' ')

Re: Identifying month date year from given field.

Thu, 12 Jun 2014 08:39:38 GMT

You mean extracting the information from Excel file name?

Regards,

Prabhu

Re: Identifying month date year from given field.

antoniotiman — Thu, 12 Jun 2014 08:45:10 GMT

Hi,

Try

Date(Date#(Title,'DD MMM YYYY'))

Month(...............

Year(.........................................

Re: Identifying month date year from given field.

ashfaq_haseeb — Thu, 12 Jun 2014 08:45:32 GMT

Hi,

Add the below code in back-end script.

LOAD Title,

[CDMS AUDIT LOG],

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

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

year(date#(Title,'DD MMMM YYYY')) as Year,

[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]);

Regards

ASHFAQ

Re: Identifying month date year from given field.

anbu1984 — Thu, 12 Jun 2014 08:46:18 GMT

Check this

Re: Identifying month date year from given field.

Thu, 12 Jun 2014 08:47:13 GMT

num(SubField(Title,' ',1),00) -> Day

SubField(Title,' ',2) -> Month

SubField(Title,' ',1) -> Year

Regards,

Prabhu

Re: Identifying month date year from given field.

Anonymous — Thu, 12 Jun 2014 08:50:27 GMT

Hi,

Try below,

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

Re: Identifying month date year from given field.

its_anandrjs — Thu, 12 Jun 2014 08:51:32 GMT

Try with

For Year

Right(Title,4)

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

For Day

Left(Title,2)

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

For Month

PurgeChar(Title,0123456789)

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

For Date

Date#(Title,'DD MMMM YYYY')

Re: Identifying month date year from given field.

antoniotiman — Thu, 12 Jun 2014 08:57:24 GMT

QV with Year,Month,Date

Re: Identifying month date year from given field.

Jason_Michaelides — Thu, 12 Jun 2014 08:58:20 GMT

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

Re: Identifying month date year from given field.

Gysbert_Wassenaar — Thu, 12 Jun 2014 09:04:50 GMT

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.

Re: Identifying month date year from given field.

Anonymous — Thu, 12 Jun 2014 09:05:09 GMT

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

Re: Identifying month date year from given field.

deepakqlikview_123 — Thu, 12 Jun 2014 10:00:43 GMT

Hello Expert,

can you please suggest how to get the day.

It is not showing perfectly.

Thanks

Re: Identifying month date year from given field.

deepakqlikview_123 — Thu, 12 Jun 2014 10:15:29 GMT

Hi Anand,

Not able to retrieve day.Can you please suggest.

Thanks

Re: Identifying month date year from given field.

deepakqlikview_123 — Thu, 12 Jun 2014 10:17:29 GMT

Hi Jason,

Thanks for reply,Can you please suggest on this.

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

Thanks

Re: Identifying month date year from given field.

its_anandrjs — Thu, 12 Jun 2014 10:25:41 GMT

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]);

Re: Identifying month date year from given field.

deepakqlikview_123 — Fri, 13 Jun 2014 05:48:27 GMT

hi anand,

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

Thanks

Re: Identifying month date year from given field.

Gysbert_Wassenaar — Fri, 13 Jun 2014 08:07:54 GMT

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

Re: Identifying month date year from given field.

its_anandrjs — Fri, 13 Jun 2014 09:47:58 GMT

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,