topic Re: Manipulation of date fields in QlikView

Manipulation of date fields

Tue, 19 Aug 2014 12:27:35 GMT

Hello all!

I have a question concerning on how to manipulate date fields. Currently I have YearMonth in the format YYYY/MM ranging from 2012/10 to 2013/09:

Due to demonstration purposes I want to transform the YearMonth field in a way that I can analyse my data only for year 2012. So, how can we change for example 2012/10 to 2013/10 and 2012/11 to 2013/11?

Thank you very much in advance!

Best regards,

Heiko

Re: Manipulation of date fields

Tue, 19 Aug 2014 12:30:47 GMT

Hi Heinko,

I have no idea why you would want to do this but you can do it this way.

left(YearMonth,4) +1 & mid(YearMonth,6,2) as Field.

Regards,
Marius

Re: Manipulation of date fields

Tue, 19 Aug 2014 12:33:21 GMT

Sorry, you would have to include '/' as well.

So do left(YearMonth,4) +1 & '/' & mid(YearMonth,6,2) as Field.

Regards,
Marius

Re: Manipulation of date fields

Tue, 19 Aug 2014 12:36:53 GMT

Hello Marius,

thanks a lot for your response. Could you please explain me shortly whats the mid function doing?

thx and regards,

Heiko

Re: Manipulation of date fields

Tue, 19 Aug 2014 12:42:19 GMT

Hi Heinko,

When you use the mid function you specify which position in a string you want to pull values out of and how many caracters.

Meaning mid(YearMonth,6 (Start at Caracter 6),2 (Fetch 2 caracters from position 6).

QlikView's example:

Substring of the string s. The result is the string starting at character n1 with the length of n2 characters. If n2 is omitted, the function returns the rightmost part of the string starting at character n1. The positions in the string are numbered from 1 and up.

mid('abcdef',3 ) returns 'cdef'.

mid('abcdef',3, 2 ) returns 'cd'.

mid( Date,3 ) where Date = 970714 returns 0714.

mid( Date,3,2 ) where Date = 970714 returns 07.

Regards,
Marius