topic Re: Month()/Day() Functions with Leading Zeros? in QlikView

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

Thu, 02 Aug 2012 09:52:32 GMT

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)

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

Thu, 02 Aug 2012 09:54:10 GMT

*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)

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

swuehl — Thu, 02 Aug 2012 10:04:55 GMT

Try

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

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

Thu, 02 Aug 2012 10:07:04 GMT

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.

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

Anonymous — Thu, 02 Aug 2012 10:26:24 GMT

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

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

Thu, 02 Aug 2012 10:56:26 GMT

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

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

swuehl — Thu, 02 Aug 2012 11:01:29 GMT

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

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

Thu, 02 Aug 2012 11:16:45 GMT

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.

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

swuehl — Thu, 02 Aug 2012 11:34:27 GMT

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.

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

Thu, 02 Aug 2012 11:45:03 GMT

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.

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

swuehl — Thu, 02 Aug 2012 12:13:23 GMT

Well, you can use variables to hold expression definitions at one place. If you need to pass the field name(s), use dollar sign expansion with parameter.

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

Thu, 02 Aug 2012 12:23:16 GMT

Is that not MORE complicated than writing a Macro function?! 🙂

What would be the benefit of using variables rather than user-defined functions?

Steve.

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

Anonymous — Thu, 02 Aug 2012 12:28:58 GMT

Variables are relaible, they always work. Macros depend on many things external to QV - client, user actions... I better use ten variables than one macro if possible.

Regards,

Michael

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

Thu, 02 Aug 2012 12:34:51 GMT

Thanks Michael,

That's handy to know... can you explain what you mean by "client/user actions" causing Macros to not work?... under what circumstances are you referring?

Cheers,

Steve.

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

swuehl — Thu, 02 Aug 2012 12:36:43 GMT

IMHO, following does not look complicated, is easy to maintain and understand.

Set MakeKey = Num(Year($1),'0000') & Num(Month($1),'00') & Num(Day($1),'00');

Set MakeKey2 = Date($1,'YYYYMMDD');

LOAD *, $(MakeKey(Date)) as Key, $(MakeKey2(Date)) as Key2 INLINE [

Date

10.06.2012

01.01.2012

];

A dollar sign expansion with parameter is not very different from a user defined function, is it?

AND you can use all QV functions and access the data model like if you've written the code inplace (that's obviously what happens, the code will be replaced by the dollar sign expansion).

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

Anonymous — Thu, 02 Aug 2012 12:41:21 GMT

Examples of the most typical situations:

Macros may work in Desktop client and IE plugin, but not in AJAX client.

Usr may allow or not allow macros, or allow "safe" when functionality requires "system access"

Unfortunately it is often impossible to avoid macros completely.

Regards,

Michael

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

Thu, 02 Aug 2012 12:50:10 GMT

Hiya Guys,

This discussion has been massively helpful (even if we have deviated off-topic slightly!) - thank you both very much 🙂

Swuehl: I agree - this isn't much different from using a user-defined function... although would you be able to explain your INLINE statement with date literals, please?

Michael: Thanks for this - these are things which I probably wouldn't have considered (until it was too late!).

I will definitely bear both you comments in mind and have a re-think! 🙂

Cheers,

Steve.

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

swuehl — Thu, 02 Aug 2012 13:01:47 GMT

I can't see where we were going off-topic, don't you want to format your Keyfield anymore like I've done with MakeKey/MakeKey2?

The INLINE is just to create a input data source that I can load and demonstrate the dollar sign expansion.

Just replace it with FROM YourTable or however you get your data in QV. The Date field values will be interpreted as dates if your standard date format is appropriate (like SET DateFormat='DD.MM.YYYY';)

Regards,

Stefan

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

Thu, 02 Aug 2012 13:05:16 GMT

Ah yes, of course - thank you.

By "off-topic" I was reffering to the slightly different discussion relating to the use of variables vs. macros 🙂 Essentially required to get the my desired result - but not exactly a direct answer to the original question 🙂

All VERY useful though and much appreciated!

Cheers,

Steve.