topic Transforming a data into a weeknumber in QlikView

Transforming a data into a weeknumber

Wed, 24 Aug 2016 11:55:53 GMT

Dear all,

I have a column called "OFF_SWITCH_DAT" with date that looks like this: DD-MM-YYYY HH:MM:SS (like this: 4-1-2016 17:48:50). What I would like to do now is transform this value into a weeknumber.

I have tried this:

WeekName((OFF_SWITCH_DAT),'-') as WeekNum,

But this gives me the following output:

Also

WeekName(OFF_SWITCH_DAT) as WeekNum,

Does not work (giving me 2016/04)

Any thougts on how I can get a weeknumber.

Cheers,

Marc

Re: Transforming a data into a weeknumber

Anonymous — Wed, 24 Aug 2016 12:00:06 GMT

use this Expression and define your datefield instead of hardcoded date

=weekname(timestamp#('4-1-2016 17:48:50','DD-MM-YYYY hh:mm:ss'))

Re: Transforming a data into a weeknumber

santiago_respane — Wed, 24 Aug 2016 12:02:52 GMT

Hi,

try something like this:

=Num(Week(YourDateField))

Or this:

=weekname(YourDateField)

Let me know if this helps.

Kind regards,

Re: Transforming a data into a weeknumber

jonathandienst — Wed, 24 Aug 2016 12:16:53 GMT

First make sure that the OFF_SWITCH_DAT is being correctly loaded as a numeric date value. The easiest way to check is to create a list box for OFF_SWITCH_DAT and seeing whether the dates align to the left (string) or right (numeric date), or a mix.

Then you can get a Week number with Week(OFF_SWITCH_DAT), or a week/year name with WeekName(OFF_SWITCH_DAT).

Re: Transforming a data into a weeknumber

ToniKautto — Sun, 28 Aug 2016 22:10:35 GMT

As suggested above the most likely reason is that the timestamp does not have the expected underlying numerical value.

If the incoming format is the same as your expected format in the app, I would suggest that you setup the app format variables to match the expected output. This should minimize your need for additional formatting.

From your example format string, I noticed that you use capital letters for the time. Try altering the format variables to lower case for the time portion. For consistency, make sure all three variables are aligned format wise.

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY ';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss';

Re: Transforming a data into a weeknumber

rupamjyotidas — Sun, 28 Aug 2016 23:35:14 GMT

Try This WeekName(Floor(NumOFF_SWITCH_DAT))).

Thanks