topic Convert Dayname to Date or Number in QlikView

Convert Dayname to Date or Number

shane_spencer — Tue, 29 May 2018 13:16:17 GMT

I've got a field that's a mix of Dayname (Mon, Tue, etc) and Date i.e 27/04/2018.

I also have a variable for Week Ending i.e. 25/05/2018 would be Friday.

I somehow need to convert the Dayname to a number i.e. Mon = 0, Tues = 1

(so that I can convert the day name into date using that variable. i.e. Variable -4 + Dayname = Date)

See attached QVD. How do I convert the Mon, Tues etc in Day/Date field to a number?

Miguel_Angel_Baeyens — Tue, 29 May 2018 13:23:24 GMT

Use Pick() and Match():

Match([Day/Date], 'Mon','Tue','Wed') // this will return 1, 2, 3 depending on the order. If 'Sun' is the first value, then Sun = 1

Then add this value to the variable and you will get the full date.

giovanneb — Tue, 29 May 2018 13:32:49 GMT

Hello Shane, try this

Best regards !

shane_spencer — Tue, 29 May 2018 13:48:56 GMT

Thanks. The nested If was exactly what I was trying to avoid though.