Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use weekday() function correctly?

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!



4 Replies
d_pranskus
Partner
Partner

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
Not applicable
Author

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

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



Not applicable
Author

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.