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)

[swuehl]

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.

[#3]

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

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

Steve.

[Anonymous]

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

[#5]

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.

[swuehl]

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

[Anonymous]

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

[#8]

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.

[swuehl]

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

[#10]

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.