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:
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!
Solved! Go to Solution.
This load script works for me:
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
[Summe von AE],
year(date(Eingangsmonat)) as year,
month(date(Eingangsmonat)) as month,
day(date(Eingangsmonat)) as day
(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?)
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.
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
It works well here, maybe you are not using IE?
Could it work if you copy paste it?
Otherwise you can access them from my QlikViews section;
Yes, im using firefox. ill try your link later thanks
im also sort of new in here. but try use something like
year(date#(field, 'DD.MM.YYYY') as year