How to use weekday() function correctly?

[#1]

Hi All,

I'm having issue applying qlikview built-in function weekday().

On variable setting, i have the following:

SET DayNames='1;2;3;4;5;6;0';

In the script, when i use the weekday function i get the correct number but i won't get the correct name when i map to an inline load:

DayNameMap:
mapping load * inline [
WeekdayNum, WeekdayName
0, Sun
1, Mon
2, Tue
3, Wed
4, Thu
5, Fri
6, Sat
0, Sun
];

When i change my OS regional setting, it doesn't change the result. I figure that when the weekday is mapped, it is treated as a number. When i use the num function, it will give me a different number. Please see attached file to get what i meant.

Any input is very appreciated. Thanks!

[d_pranskus]

Hi

Actually Weekday() function returns a weekday value, which is of type Dual. It has it's numeric value and textual representation. When you set SET DayNames='1;2;3;4;5;6;0' you are defining textual representation of the weekdays values. But the result of the numeric counterpart of WeekDay function value does not change. Here is a list of Text, Num pairs, you get as a result of WeekDay function

For Mon (1, 0)

For Tue (2, 1)

For Wed (3, 2)

For Thu (4, 3)

For Fri (5, 4)

For Sat (6, 5)

For Sun (0, 6)

When you call applymap function, actualy it uses numeric counterpart of the value. But as visual representation by default it shows textual representation you defined in WeekNames variable. (Try to change format of Weekday field from Mixed to Numeric and you should see that values representation changes).

To get a numeric value from it's textual representation try to use EVALUATE() function.

As to me I never use any mappings for weekdays.

Cheers

Darius

[#3]

Thanks so much Darius. I read in Ref Manual that this function is affected by the OS regional setting but it doesn't really explain what is the correct setting.

Do you know if the setting set in variable overwrite the OS regional setting ? Or Monday will always be 0 in Qlikview?

[d_pranskus]

Hi

Actually I don't know for sure, but it seems, that WeekDay returns 0 for Monday.

To create Weekday field for Sun=0 try this expression

DUAL(TEXT(Weekday(TempDate)), MOD(Weekday(TempDate) +1, 7)) AS WeekDay

It will create a Dual type field with a text of weekday and value with 0 for Sunday

Cheers

Darius

[#5]

We have a field called DayOfWeek that is created using the WeekDay() function. In one of our document files the values are listed as text (Sun, Mon, etc.). In another document using the exact same script to load DayOfWeek Qlikview is showing numeric data (0, 1, etc.). We want text in both documents what would be causing one to show in one format (text) and the other the other format (numeric)?

We want the text, Sun, Mon, etc.

Thanks.