topic Re: Creating calendar in QlikView

Creating calendar

Anonymous — Thu, 30 Mar 2017 07:04:54 GMT

Hello All,

I am trying to create a calendar , but it throws error basically it is not able to capture the date inside the variable vMinDate and vMaxDate. However if i do $(vMindate) and $(vmaxDate) it returns the date appropriately.

set vMinDate = date(min(date_va));

set vMaxDate = date(max(date_va));

test:

LOAD * INLINE [

    id, date_va

    1, 02/11/2015

    2, 03/23/2016

    3, 11/20/2016

    4, 09/22/2017

    5, 05/22/2012

];

tempcalendar:

load

$(vMinDate) +IterNo()-1 as strtDateNum,

     $(vMinDate) +IterNo()-1 as startdate

     AutoGenerate 1

     while $(vMinDate) + IterNo()-1 <= $(vMaxDate);

Re: Creating calendar

karthiksrqv — Thu, 30 Mar 2017 07:07:15 GMT

Hi,

It seems to be working as expected, could you elaborate on what it is that you are looking for?

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 07:15:57 GMT

Thanks karthik for prompt response.

But it dosent work it throws error, basically i was looking for a short work around creating master calendar. So if i refer (IMG 1 below i wanted in vMinDate if i can get the values calculate from within the script/ field .

my scipt

set vMinDate = date(min(date_va));

set vMaxDate = date(max(date_va));

but when i reload it shows error as shown in question

IMG 1

Re: Creating calendar

Anil_Babu_Samineni — Thu, 30 Mar 2017 07:21:02 GMT

May be date_va is the field is not working for you

Can you check in Text box -- =$(vMinDate)

Re: Creating calendar

karthiksrqv — Thu, 30 Mar 2017 07:25:16 GMT

These are the issues am seeing:

1. The 2 variables are referring to a field that is created after the variables are defined.

2. You cannot use date(min(date_va)) , this is how we'd use it in a chart, not in the script. In the script, you'd have to do a

temp_min_table:

load

min(date_va) as test_min_date,

max(date_va) as test_max_date

resident

test;

and then

set vMinDate = peek('test_min_date',-1,'temp_min_table');

set vMaxDate = peek('test_max_date',-1,'temp_min_table');

You can then use the vMinDate and vMaxDate in your next reload.

Hope this helps.

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 07:26:25 GMT

yes anil.using text box it work, as mentioned earlier

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 07:28:47 GMT

yea i checked on this as an alternative i know this approach.

like we have to create a field and then load min max date in variable using peek function..

so i was being curious why can t we procees just by storing min/max in variable itself i was not confident with the reason as to why we cant do straight away..

Re: Creating calendar

karthiksrqv — Thu, 30 Mar 2017 07:44:52 GMT

It's just the way the Qlik syntax work. All aggregations in the script level happen via a load statement, in the chart level, they are similar to 'functions' that we can use.

Re: Creating calendar

PrashantSangle — Thu, 30 Mar 2017 07:45:54 GMT

Hi,

try below

set vMinDate = '=date(min(date_va))';

set vMaxDate = '=date(max(date_va))';

Regards

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 08:41:27 GMT

set vMinDate = '=date(min(date_va))';

set vMaxDate = '=date(max(date_va))';

below error

Re: Creating calendar

Frank_Hartmann — Thu, 30 Mar 2017 08:58:27 GMT

Maybe like that:

test:

LOAD * INLINE [

id, date_va

1, 02/11/2015

2, 03/23/2016

3, 11/20/2016

4, 09/22/2017

5, 05/22/2012

];

Temp:

Load

min(date_va) as minDate,

max(date_va) as maxDate

Resident test;

Let vMinDate = Num(Peek('minDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

tempcalendar:

load

$(vMinDate) +IterNo()-1 as strtDateNum,

$(vMinDate) +IterNo()-1 as startdate

AutoGenerate 1

while $(vMinDate) + IterNo()-1 <= $(vMaxDate);

hope this helps!

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 09:24:34 GMT

Thanks Frank

this wud work 100%..

so i was checking why cant we directly store min/max dates directly in variables(vMinDate and vMaxDate)

something like

set vMinDate = date(min(date_va));

set vMaxDate = date(max(date_va));

Re: Creating calendar

Anonymous — Thu, 30 Mar 2017 09:28:02 GMT

i could also have done this

Temp:
Load
min([Order Date]) as minDate,
max([Order Date]) as maxDate
FROM

(ooxml, embedded labels, table is Orders);

TempCalendar:
LOAD
minDate + Iterno()-1 As Num,
Date(minDate + IterNo() - 1) as TempDate
Resident Temp
While minDate + IterNo() -1 <= maxDate;

would it make any difference in performance if I directly accesa fields minDate,maxDate above in TempCalendar