topic Re: Re: Setting Begin and End Dates in Script in QlikView

Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 17:06:59 GMT

This should be simple but it is the fist time I have had to do this

If I want to have a beginning and ending reporting month variable set at the beginning of my script what is the proper function/syntax

What I want is :

Begin Month Date = 01/01/2012

End Month Date = 06/30/2012

Not sure of the exact syntax or if LET or SET is the proper way to establish.

Thanks in advance,

Steve

Re: Setting Begin and End Dates in Script

jjordaan — Mon, 02 Sep 2013 18:07:42 GMT

Hi Zagzebski,

You can use something like this;

LET vStartMonth = MontStart($(vToday));

LET vEndMonth = MonthEnd($(vToday));

Re: Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 18:14:28 GMT

Thanks Jereon - Is there a way to hard code the dates I refereence above...Begin Date = 1/1/2012 and End Date= 6/30/2012 in the variable?

Re: Setting Begin and End Dates in Script

jjordaan — Mon, 02 Sep 2013 18:27:48 GMT

Yes you can do it like this:

LET vDate = Date#('2015-12-31');

Re: Setting Begin and End Dates in Script

kedar_dandekar — Mon, 02 Sep 2013 18:36:55 GMT

Hi,

Just another way to do so would be using MakeDate function:

LET vBeginMonthDate = MakeDate(2012, 01, 01);

LET vEndMonthDate = MakeDate(2012, 06, 30);

HTH,

Re: Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 19:44:45 GMT

Thanks KD

To utilize the variable what do I need to do? I tried to use it in a table I was loading but it says field not found?

Re: Setting Begin and End Dates in Script

kedar_dandekar — Mon, 02 Sep 2013 20:17:35 GMT

Hi Zagzebski,

Are you planning to use the Begin and End date variables to generate your master calendar data.

or you want to use these variables to filter data from a table while loading?

- KD

Re: Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 20:20:35 GMT

KD -

I need to use them in a WHERE clause in a couple of the tables I am loading.

Re: Re: Setting Begin and End Dates in Script

kedar_dandekar — Mon, 02 Sep 2013 20:26:03 GMT

Hi Zagzebski,

In that case you can use a string variable, in place of a date variable,

and use it with $() function in your where clause while loading.

Refer attached QVW for details..

The script contains related comments..

HTH,

Re: Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 20:46:02 GMT

Thanks KD - works!

Two quick follow ups:

1. This is part of a transform load so I want these dates to be available when I reload this data into my model. How can I get this so it loads into my variables of my model?

2. Also not 100% sure in the difference between the let variables and the set variables.

Really apprecaite your help on this, thanks again.

Steve

Re: Re: Setting Begin and End Dates in Script

kedar_dandekar — Mon, 02 Sep 2013 21:06:04 GMT

Hi Zagzebski,

1. Pardon me, but I did not get the Transform load part, can you please elaborate on this (some example would help).

2. As regards to the SET and LET, the main difference is that:

SET assigns the value on the right of the "=" sign as is, without evaluation (string format), and

LET evaluates the expression on the right of the "=" sign before assigning it to the variable.

Simple example would be:

SET vDateExp1 = 3 + 4; //This stores the string '3 + 4' in the variable, whereas

LET vDateExp2 = 3 + 4; //Evaluates the right hand side to 7 and stores this value in the variable

- KD

Re: Setting Begin and End Dates in Script

zagzebski — Mon, 02 Sep 2013 21:33:55 GMT

I create a QVD off the file with these date variables. I was just wondering the best way to "move" that variable to the QVD file so I can upload it to a new QVW file.

Re: Re: Setting Begin and End Dates in Script

kedar_dandekar — Mon, 02 Sep 2013 21:57:24 GMT

Hi Zagzebski,

Since you are creating a QVD file using these date variables (in the WHERE clause), the data in the QVD would already get filtered by the dates and stored into QVD file, right?

I am sorry, I did not quite get that part - 'Best way to Move the variable to the QVD file'.

Refer attached QVW, in this, I have used the Date variable created using LET assignment, to filter table data (just another way), not sure if this is what you were looking for ..

HTH,