topic Qlikview Scripting : function date in QlikView

Qlikview Scripting : function date

Fri, 25 Sep 2015 13:12:55 GMT

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 !

Re: Qlikview Scripting : function date

avinashelite — Fri, 25 Sep 2015 13:17:12 GMT

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

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:19:43 GMT

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

Re: Qlikview Scripting : function date

avinashelite — Fri, 25 Sep 2015 13:22:48 GMT

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

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:28:24 GMT

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

Re: Qlikview Scripting : function date

Peter_Cammaert — Fri, 25 Sep 2015 13:38:30 GMT

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

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:39:59 GMT

it juste one dimension Resource

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:45:33 GMT

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

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:52:35 GMT

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

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 13:55:35 GMT

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

Re: Qlikview Scripting : function date

Peter_Cammaert — Fri, 25 Sep 2015 14:00:57 GMT

Try with this script and the Excel file you first posted in the same directory.

Dabord:

CROSSTABLE (Date, Nombre)

LOAD 'P ' & RowNo() AS Propriété,

FROM [.\Qlikview scripting.xlsx](ooxml, embedded labels, table is Feuil1);

Données:

NOCONCATENATE

LOAD Propriété, Date(num#(Date)) AS Date, Nombre

RESIDENT Dabord;

DROP Table Dabord;

Calendrier:

LOAD DISTINCT Date,

Week(Date) as Semaine,

WeekDay(Date) as Semaine_Jour,

WeekName(Date) as Semaine_Nom,

WeekYear(Date) as Semaine_Année

RESIDENT Données;

Best,

Peter

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 14:02:28 GMT

could you send me the script on qvs format please?

Re: Qlikview Scripting : function date

Peter_Cammaert — Fri, 25 Sep 2015 14:02:35 GMT

If you don't add values for dimension Resource, you won't be able to make selections of different rows.

Re: Qlikview Scripting : function date

Peter_Cammaert — Fri, 25 Sep 2015 14:03:38 GMT

Copy/Paste from my last post. It's the complete script from the QVW, except for the localized SET statements (which are created automatically in every new document)

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 14:04:59 GMT

ok Thank you, i will try it

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 14:11:53 GMT

Thank you Peter , it works

Thank you all for your help !

Re: Qlikview Scripting : function date

Fri, 25 Sep 2015 15:03:09 GMT

Thank you Adam for your help, it's solved