topic Days from numbers? in QlikView

Days from numbers?

Thu, 28 Apr 2011 15:44:10 GMT

Hello Experts,

In in my Time Dimensions tables, the weeks are linked to days in the form of numbers, which I think is common. So in the Table for week 17, the week days show as 1,2,3 to 7 and my week here starts from Sunday, 1 being Sunday. I want to generate days from this numbers. 1- Sunday, 2 - Monday and save it as the Time_Week_Day, how can i achieve this?

Thanks in advance,

ANDY

AW:Days from numbers?

brenner_martina — Thu, 28 Apr 2011 16:19:15 GMT

Hi, Andy,

look in the helpfile for DUAL:

dual
dual( s , x )
Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Example:
load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep
Sunday,1
Monday,2
Tuesday,3
Wednesday,4
Thursday,5
Friday,6
Saturday,7
];

load Date, weekday(Date) as DayOfWeek from afile.csv;

The script example will generate a field DayOfWeek with the weekdays written in clear text. QlikView will for all purposes regard the field as a numeric field.

AW:Days from numbers?

erichshiino — Thu, 28 Apr 2011 16:38:38 GMT

Dual() is a very powerful function in QlikView

As alternative, you can use date() to format your dates.

You could use:

Load Date, date(Date,'WWW') as DayoftheWeek, weekday(Date) as nDayoftheWeek

resident ...

this will be still a date formated as text. To force it to become a text, you can use text(date(Date,'WWW'))

this will get the names on the variable defined on the begging of the script

SET

you could also create the line below and use 4 w's on the formating condition to get a full name

DayNames ='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Set LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';