Month()/Day() Functions with Leading Zeros?

[#1]

Hiya Guys,

I am writing a user-defined function in a Macro and require Months and Days in the format of "00".

I assume that, as I am running the function from a Load Script, the following line of code does not work:

MakeDateKey = ActiveDocument.Evaluate("Num(Year(dat),'0000') & Num(Month(dat),'00') & Num(Day(dat),'00')")

So how do I convert/format the following so that Month() and Day() will always return a 2-digit number?:

Cheers,

Steve.

MakeDateKey = Year(dat) & Month(dat) & Day(dat)

[#2]

*Edit:

So how do I convert/format the following so that Month() and Day() will always return a 2-digit number?:

MakeDateKey = Year(dat) & Month(dat) & Day(dat)

[swuehl]

Try

Num(Year(dat),'0000') & Num(Month(dat),'00') & Num(Day(dat),'00') as MakeDateKey

[#4]

Thanks swuehl,

This is what I did originally - however I will be using this in several places so I require it within a Macro function (where the "Num" function isn't recognised).

Any other thoughts?  🙂

Cheers,

Steve.

[Anonymous]

Actually it is simpler than that:

date(dat, 'YYYYMMDD')

Speaking of macro - you can use Evaluate, where any QV function can be used.

Regards,

Michael

[#6 · Accepted solution]

Hiya Michael,

As I'm running this within a Macro - the above function won't work (the single quote denotes a comment, and the function Date(dat,"YYYYMMDD") doesn't exist).

Unfortunately, you cannot use ActiveDocument.Evaluate from a Load Script as the Document doesn't technically exist until after the refresh (see here: http://community.qlik.com/message/209607#209607).

I am currently using the following IF statements to achieve the desired result (it's not ideal though!):

If anyone has any other thoughs please let me know!

Cheers,

Steve.

'*** Return DateKey (YYYYMMDD) from DateTime ***'
Public Function MakeDateKey(dat)

'*** Return -1 for unknown ***'
If (dat = "") Then
MakeDateKey = -1
Else
strYear = CStr(Year(dat))

'*** Add leading zero if required ***'
If ((Month(dat)+0) < 10) Then
strMonth = CStr("0" & Month(dat))
Else
strMonth = CStr(Month(dat))
End If

'*** Add leading zero if required ***'
If ((Day(dat)+0) < 10) Then
strDay = CStr("0" & Day(dat))
Else
strDay = CStr(Day(dat))
End If

MakeDateKey = (strYear & strMonth & strDay) + 0 ' Add "0" to Cast to number

End if

End Function

[swuehl]

Could you elaborate why you want to use a Macro at all for doing this?

[#8]

Hiya swuehl,

This is unfortunately a side-effect from a previous question I have posted:  http://community.qlik.com/thread/58568?tstart=0

I need to perform some transformations of the data - althought they need to be performed on the QV-side due to restrictions connecting via ODBC.  My ultimate goal is to automatically refresh the data periodically - so I need all the control to be within the QV Load Script.

If there is a better methedology/best-practice way for achieving this then please let me know!  Otherwise I am destined to re-write my VBA functions from MS Access in QV VBS.

Cheers,

Steve.

[swuehl]

Ok, I've read your other thread, but I am still unclear why you want to use macros for something like IIF or NZ, where you could use QV builtin functions instead in your load script (within the LOAD part, not the SQL SELECT part).

Probably I miss some essential requirement.

[#10]

Hiya swuehl,

Thanks for your reply.

If I was to use:

LOAD Num(Year(DateTimeCallOpened),'0000') & Num(Month(DateTimeCallOpened),'00') & Num(Day(DateTimeCallOpened),'00') as DateCallOpened_Key ...

...within my Script for the Date Key of when a Call was opened, then I would have to use the same line of code for calculating the Date Key for when a Call was closed... and for the open/closed times, etc.

This duplication of code is a lot of work, prone to errors, and is not maintainable if the forumla changes in the future.

Creating a user-defined function to run this transformation is much more preferable... unless you know of a better way?

I am new to QlikView (without any formal training) - and I am a Software Engineer by trade! - so I am happy to listen to suggestions for better ways to achieve my desired result.

Cheers,

Steve.