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]

ok, i saw your example. now it should work.

table:

LOAD Projektauftrag,

     Eingangsmonat,

     Kundenname,

     PLZ,

     Vertreter,

     Vertretername,

     [Summe von AE],

     year(date(left(Eingangsmonat,10),'DD.MM.YYYY')) as year,

     month(date(left(Eingangsmonat,10),'DD.MM.YYYY')) as month,

     day(date(left(Eingangsmonat,10),'DD.MM.YYYY')) as day

FROM ......

[gandalfgray · Accepted solution]

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?)

[#4]

well yeah my first suggest workinf fine here.

only problem was you commented all set values and your load script was hmm dunno something strange for me.

[#5]

@ Gandalf

there is Use advanced editor option right top side in replay section.

[#6]

Dear all,

your last suggestions worked, Muncho & Gandalf,

so thank you very much!!

[#7]

Glad to hear this ^^

[ToniKautto]

You really should not uncomment the initial locale formats, since then the format is decided by the local machine whihc might not always be predictable.

If you create a list box and set the dimension to an expression =num(Eingangsmonat)  you will see the underlaying numeric value of your date field. In this case they all are integers, meaning there is no timepart so no need to Floor() in order to eliminate time part.

Instead if reformatting a lot I would arrange the load as a preceeding load in order to make it a bit easier to manage, by the same principle as I create my calendar in the link i posted above.

TableName:

LOAD

     *,

     Year(Eingangsmonat) as year,

     Month(Eingangsmonat) as month,

     Day(Eingangsmonat) as day   

     ;

LOAD Projektauftrag,

     Date(Eingangsmonat) as Eingangsmonat,

     Kundenname,

     PLZ,

     Vertreter,

     Vertretername,

     [Summe von AE],

FROM

qlikviewdata.xlsx

(ooxml, embedded labels, table is Tabelle1);

For using the Date() and Date#() fucntions in general you shoudl always keep the Dual aspect in mind.

The Date#() function evaluates the expression as a date according to the string given as format-code.

The Date() function formats the expression as a date according to the string given as format-code.

For this reason you usually will see that Date(Date#('2010-01-01')) is called when setting a date based on a input textual date.

This can be a bit tricky to grasp at a start, so just play around with it and see what both the numeric and textual values are after you perform operations on them. The time and date functions in QlikView are really efficient once you get a hang of how if works.

Finally, it is still recommended that you have a calendar table to handle the convertions from date to year, month, quarter etc. It will make your model easier to interpret and also give your application a consistent calendar. Also in larger applications you will not fill the table with reoccuring convertions, but have them only once in the calendar table.

[gandalfgray]

Thanks Muncho

not easy to see that link...

[#10]

Hi, I need reverse process. I have 3 fields with Day, Month and Year and would like to get one Date field. Can somebody help me with that? Thanks

[swuehl]

This thread is more than 2 years old, so you shouldn't expect an answer, but you are lucky 😉

Try

LOAD

     YearField, MonthField, DayField,

     MakeDate(YearField, MonthField, DayField) as DateField

FROM ...;