topic Re: Date in QlikView

Date

rdlccn70 — Wed, 10 Feb 2016 16:53:18 GMT

Hello,

I would like the following date format "001.2016" divided in month and year. -> 001 and 2016 or 1 and 2016

Name of the dimension is DATAEGV_PERIODE

The data comes in this form:

001.2016

002.2016

.....

012.2016

Does anyone have this idea?

Thanks in advance.

Erdal

Re: Date

swuehl — Wed, 10 Feb 2016 17:06:30 GMT

Something like

LOAD *,

Month(YearMonth) as Month,

Year(YearMonth) as Year;

LOAD *,

MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth;

LOAD DATAEGV_PERIODE

FROM YourTable;

Re: Date

Wed, 10 Feb 2016 17:08:11 GMT

You can try MakeDate(subfield(FieldName,'.',2) , subfield(FieldName,'.',1)

use the date # function

Date#(right(fieldname,7),'MM.YYYY')

Re: Date

rdlccn70 — Wed, 10 Feb 2016 18:24:10 GMT

Hi,

i have tried with this code:

LOAD DATAEGV_PERIODE

FROM

W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

LOAD MonthName(Left(DATAEGV_PERIODE,3)) as Month,

YearName(Right(DATAEGV_PERIODE,4)) as Year

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

LOAD MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

The output is confused:

Do you have any idea?

Thanks

Erdal

Von: swuehl

Gesendet: Mittwoch, 10. Februar 2016 18:08

An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

Betreff: Re: - Date

Re: Date

rdlccn70 — Wed, 10 Feb 2016 19:16:18 GMT

Hi,

it was confused, but it works with:

	Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Month,
	Date#(right(DATAEGV_PERIODE,4),'YYYY') asYear

Thanks alot.

Regards,

Erdal

Re: Date

swuehl — Wed, 10 Feb 2016 19:45:09 GMT

You've used

LOAD MonthName(Left(DATAEGV_PERIODE,3)) as Month,

YearName(Right(DATAEGV_PERIODE,4)) as Year

FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

(ooxml, embedded labels);

Here, Monthname, and YearName functions expect a Date as argument, not the Year resp. Month value.

Check how I've first interpreted the date, then how I've created Year and Month from that date in a so called preceding load.

Re: Date

PradeepReddy — Thu, 11 Feb 2016 05:40:04 GMT

another way..

Month(Date#('001.2016','MM.YYYY')) as Month,

Year(Date#('001.2016','MM.YYYY')) as Year

replace '001.2016' with your date field

Re: Date

avinashelite — Thu, 11 Feb 2016 05:44:01 GMT

try like

For 001 & 2016 fromat:

LOAD

subfield(DATAEGV_PERIODE,'.',1) as Month,

subfield(DATAEGV_PERIODE,'.',2) as Year

from

table ;

For 01 & 2016 fromat:

LOAD

right(subfield(DATAEGV_PERIODE,'.',1),2) as Month,

subfield(DATAEGV_PERIODE,'.',2) as Year

from

table ;

Re: Date

jagan — Thu, 11 Feb 2016 06:21:28 GMT

Hi,

You can also try like this

Load

MakeDate(SubField(DATAEGV_PERIODE, '.', -1), SubField(DATAEGV_PERIODE, '.', 1) AS Date,

Regards,

jagan.

Re: Date

Thu, 11 Feb 2016 06:45:23 GMT

Hi Erdal,

Try this,

Month(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Month,

Year(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Year

Regards

Re: Date

rdlccn70 — Thu, 11 Feb 2016 09:17:36 GMT

Hi,

many thaks,

i have solved with:

Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

Regards,

Erdal

Von: swuehl

Gesendet: Mittwoch, 10. Februar 2016 20:46

An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

Betreff: Re: - Date

Re: Date

rdlccn70 — Thu, 11 Feb 2016 09:17:36 GMT

Hi,

many thanks,

i have solved with:

Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

Regards,

Erdal

Re: Date

rdlccn70 — Thu, 11 Feb 2016 09:18:13 GMT

Hi,

many thaks,

i have solved with:

Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

Regards,

Erdal

Von: Lokeshwari N

Gesendet: Donnerstag, 11. Februar 2016 09:45

An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

Betreff: Re: - Date

Re: Date

Thu, 11 Feb 2016 12:27:19 GMT

LOAD
(mid(DATAEGV_PERIODE,5,4)) as Year,

(mid(DATAEGV_PERIODE,1,3)) as Month

FROM
Table

Re: Date

swuehl — Thu, 11 Feb 2016 13:18:21 GMT

Erdal Cücen wrote:

Hi,

many thaks,

i have solved with:

Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

If you do it like this, you won't get your year and month parsed in correctly (i.e. you will get values with an underlying numeric value based on a date, not on the commonly used month numbers 1-12 or years.

Maybe you won't notice any issues untill you try to make e.g. selections or numeric comparisons.

If you don't know what I am talking about, create a list box for Jahr field with an Expression

=num(Jahr)