Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Divide date into day, month and year

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!

Tags (1)
1 Solution

Accepted Solutions
gandalfgray
Valued Contributor

Re: Divide date into day, month and year

This load script works for me:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

Directory;

LOAD Projektauftrag,

     Eingangsmonat,

     Kundenname,

     PLZ,

     Vertreter,

     Vertretername,

     [Summe von AE],

year(date(Eingangsmonat)) as year,

month(date(Eingangsmonat)) as month,

day(date(Eingangsmonat)) as day   

FROM

qlikviewdata.xlsx

(ooxml, embedded labels, table is Tabelle1);

// Note that the date() is not necessary in this case, I kept it anyway

(by the way, how do you attach documents to posts?)

19 Replies
Not applicable

Divide date into day, month and year

i think something like

year(date(field_name)) as year,

month(date(field_name)) as month,

day(date(field_name)) as day

Employee
Employee

Divide date into day, month and year

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

Not applicable

Re: Divide date into day, month and year

@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.

Not applicable

Divide date into day, month and year

Hi Toni you link doesn't work for me.

Employee
Employee

Divide date into day, month and year

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

Employee
Employee

Re: Divide date into day, month and year

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

Not applicable

Re: Divide date into day, month and year

Please use the example files...

Not applicable

Re: Divide date into day, month and year

@ 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

Not applicable

Re: Divide date into day, month and year

did not work either... But thanks!

Maybe someone can play with the example files I posted above

Community Browser