Divide date into day, month and year

[#1]

Hi,

I am sort of a newbie to Qkliview, so I hope someone can help me with the following problem.

In my database I have a field called "Eingangsmonat", which looks originally like this:

e.g. : "01.04.2011  00:00:00"

First I thought the 00:00:00 could be a problem, but when I select it as a field, I can change the date format, so Qlikview

recognizes the input data as a date.

Now I did the following in the script:

LOAD Eingangsmonat,

  year(Eingangsmonat) as Jahr,

  month(Eingangsmonat) as Monat,

  day (Eingangsmonat) as Tag;

It shows no error, but after running the script, I do not have new fields like Jahr, Monat, Tag....

What am I doing wrong?

How can I change the script to make it work?

Or alternatively: Can I use the existing Field "Eingangsmonat" and kind of "manipoulate it", to be able to select a whole year?

When I change format to YYYY, then I have every year twelve time (as I have 01.01.2010, 01.02.2010, 01.03.2010 behind it).

Thanks in advance for any tip!

[#2]

i think something like

year(date(field_name)) as year,

month(date(field_name)) as month,

day(date(field_name)) as day

[ToniKautto]

You need to keep in mind that dates are of type dual, which means they have a numeric decimal value and a textual presentation. For QlikView to recognize your date values successfully you need to set the DateFormat variable properly in your script start. An alternative is to add the format you use in the functions where you reformat the values.

The recommended approach is in general that you have your date in the fact table and then create a master calender table to where the date is linked. This way you can select a year on the calendat year field and get the corresponding selection in your fact table.

http://community.qlik.com/qlikviews/1022

[#4]

@Muncho:

no error, but I still can not select any new field like year, month or day...

Other guesses?

@Toni:

I can not open your link.

[#5]

Hi Toni you link doesn't work for me.

[ToniKautto]

You also need to keep in mind that date are decimal values, hence they have both a date (integer part) and a time (decimal part). To only work with the date value you might need to use Floor() to trim away the decimal part.

Please provide a sample QVW to make advising a bit easier. In case your app contains sensitive data, please user scrambling to make content unreadable;

Settings > Document Settings > Scrambling

[ToniKautto]

It works well here, maybe you are not using IE?

Could it work if you copy paste it?

http://community.qlik.com/qlikviews/1022

http://community.qlik.com/download-qlikview.jspa?qlikview=1022

Otherwise you can access them from my QlikViews section;

http://community.qlik.com/view-profile-qlikviews.jspa?view=qlikview&username=tko

[#8]

Please use the example files...

[#9]

@ Toni

Yes, im using firefox. ill try your link later thanks

@ Jan

im also sort of new in here. but try use something like

year(date#(field, 'DD.MM.YYYY') as year

[#10]

did not work either... But thanks!

Maybe someone can play with the example files I posted above