topic Re: How to separate a fields in QlikView

How to separate a fields

Mon, 23 Mar 2015 15:13:12 GMT

Hi,

I have some dates coming in in MM/YYYY format and I want to separate those out as Months, and Years. For some reasons I won't get into I cannot use Month(Date) as Month and Year(Date) as Year since the time formats are a little out of whack at the moment.

The other issue I have is that some for instance January is not 01 rather 1 so I cannot use a number to count the separation but would rather use the '/'.

So how can I take everything to the left of the / and call it month and everything to the right of the / and call that year? The field is called Date.

Re: How to separate a fields

datanibbler — Mon, 23 Mar 2015 15:16:02 GMT

Hi Jason,

maybe your date comes as a STRING, not in proper date_format? In that case, try SUBFIELD - you can use the '/' as separator to split out the parts.

HTH

Re: How to separate a fields

Mon, 23 Mar 2015 15:18:22 GMT

Hi,

Can you try this?

left(' MM/YYYY',index( ' MM/YYYY', '/' )) as MONTH

Re: How to separate a fields

buzzy996 — Mon, 23 Mar 2015 15:20:54 GMT

try this,

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ':' ,1) returns NULL if S is an empty string

subfield(S, ':' ,1) returns an empty string if S is ':'

Re: How to separate a fields

sunny_talwar — Mon, 23 Mar 2015 15:22:34 GMT

You can try this:

Month(Date#(Date, 'MM/YYYY')) as Month

Year(Date#(Date, 'MM/YYYY')) as Year

MonthName(Date#(Date, 'MM/YYYY')) as MonthYear

HTH

Best,

Sunny

Re: How to separate a fields

Mon, 23 Mar 2015 15:26:15 GMT

Thank you!! That is exactly what I was trying to do but I gave up. I wrapped the Date# function too early, I had it before Month and Year which is why it wasn't working before. Thanks again.

Re: How to separate a fields

sunny_talwar — Mon, 23 Mar 2015 15:29:48 GMT

Not a problem. I am glad I was able to help.

Best,

Sunny