Qlikview Scripting : function date

[#1]

Hello all,

I need your help to create a script in Qlikview. I have a excel file, it's contains date (jj/mm/yy) and values of each date.

I need to convert date to day, for example if i have 25/09/2015, it's corresponds to Friday. and when we define all days for the date, we should determine the week, for example we suppose after the conversion we have : Friday saturday sunday monday tuesday wednesday thursday friday saturday sunday we can determine that for the week1 : we have friday saturday sunday and week2 we have : monday tuesday wednesday thursday friday saturday.

I thought to choose day fuction for the conversion date to day. but i don't have any idea how i can determine the week.

Thank you for your help!

Bonjour à tous,

J'ai besin de votre aide pour développer un script sous Qlikview. en effet j'ai un fichier excel qui contient des dates dans les headers, et des valeurs pour chaque colonne date. ce que je voudrais faire c'est par exemple si je trouve 25/09/2015 je peux déterminer le jour qui correspond à cette date et donc déduire qu'il s'agit d'un vendredi. et après avoir déterminer les jours, je voudrais déduire la semaine. On suppose que je trouve après la conversion Mercredi jeudi vendredi samedi dimanche lundi mardi mercredi jeudi vendredi samedi dimanche, je peux dire que le week 1: Mercredi jeudi vendredi samedi dimanche et week2: lundi mardi mercredi jeudi vendredi samedi dimanche.

J'ai pensé à utiliser la fonction day pour déduire le jour à partir d'une date, mais je n'ai aucune idée comment je peux faire pour déterminer les semaines.

Merci pour votre aide !

[avinashelite]

try this functions:

week(date)

Week number. Returns an integer representing the week when the fraction of expr is interpreted as a date according to the standard number interpretation.

Example:

week( '1971-10-30' ) returns 43.

If the date format used does not correspond to the one set in the operating system, QlikView will not be able to make a correct interpretation. See above under day(date).

weekname(date),

weekday(date)

day(date)

etc

[#3]

you can use the weekday() function if you need to determine the day of the week the date falls on

• weekday(date)
• Week day. Returns an integer between 0-6.
• Example:
• weekday( '1971-10-30' ) returns 5.
• If the date format used does not correspond to the one set in your operating system, QlikView will not be able to make a correct interpretation. See above under day(date).

or you can use the week() function to determine which week within the year

• week(date)
• Week number. Returns an integer representing the week when the fraction of expr is interpreted as a date according to the standard number interpretation.
• Example:
• week( '1971-10-30' ) returns 43.
• If the date format used does not correspond to the one set in the operating system, QlikView will not be able to make a correct interpretation. See above under day(date).

for either function you need to pass in the date

[avinashelite]

try this:

LOAD A,

Week(A) as A_Week,

WeekDay(A) as Week_Day,

WeekName(A) as Week_Name,

WeekYear(A) as Week_Year

FROM

(ooxml, no labels, table is Feuil1);

[#5]

yes i try it, but i have a problem when i choose my file to loaded it.

I can't see the headers with the date, i see only F G H....

[Peter_Cammaert]

You'll need to serialize your data. The matrix rotation can be done using a CROSSTABLE LOAD. I prefer to give you an example with your own (Excel) data, but I'm not sure what property the different rows relate to? There are 25 rows, so it can't be the hour of the day. What dimension value should be put before column A?

Peter

[#7]

it juste one dimension Resource

[#8]

you can try change the options up on the top right to say Embedded for labels and set the lines for headers back to 0

The option of Explicit lables may work but I would try embedded first - does the actual Excel file have headings in it on the first row

[#9]

yes i tried to change the options before, but it doesn't work

[#10]

it looks like there are not any column headers in the file - you then will most likely need to do a Load As and for each letter, give it a field name that would correspond or update the excel file to have column headers