Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
srikantj
Contributor

calendar date

Hi qlikss,

Need some expalanation

i have calendar script:

LOAD

       min(datefield) as DateMin

       ,max(datefield) as DateMax

;

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

to find max and     min values of date, we can directly use max,min functions.why we need last 2 lines in calendar script.

I mean  these 2 lines

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: calendar date

You could skip the 2 lines if you direct pull data from the table with the field 'date'.

LOAD
date(DateMin + IterNo()) as [date]        
WHILE DateMin + IterNo() <= DateMax     
;

LOAD
min(datefield)-1 as DateMin
,
max(datefield) as DateMax
RESIDENT [Your table name];

18 Replies
prma7799
Honored Contributor III

Re: calendar date

with the help of this (FieldValue('date', RecNo()) ) you will get min or max date

You are creating dates your own using below script

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

Partner
Partner

Re: calendar date

Hi Srikanth Jetti,

This part creates a dataset containing a date for every possible 'date'.

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

Without this you should make a resident to the table where date exists. Because without any data you can't make a min or max value. But you're right when you just pull the min and max data from the table where the date field exists.

srikantj
Contributor

Re: calendar date

Can't I get min,max dates without these 2 lines.

Min(date) is enough to find minimum date

Same goes for maximum

prma7799
Honored Contributor III

Re: calendar date

You can get min or max like this also

LET vDataMax= fLOOR(Today());

LET vDataMin=Floor(date(MakeDate(2011,04,01)));

srikantj
Contributor

Re: calendar date

Still I didn't get you.

We already have date values in date field. What do you mean by creating dataset again.

I just reloaded these 2 lines by commenting top 2 lines.

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

After reload I got all ? Marks in datefield

Is these ? Marks are dataset

srikantj
Contributor

Re: calendar date

Yes I know this. I am supporting one qlikview application develop by someone.

I just want to know why he scripted like this

prma7799
Honored Contributor III

Re: calendar date

Temp:

LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

LOAD

       min(datefield) as DateMin

       ,max(datefield) as DateMax

resident Temp;

Partner
Partner

Re: calendar date

What is the name of the table where the data field exists?

You've got 2 options for getting min and max dates.

1. Directly from your table:

LOAD

MIN(Date) AS MinDate,

MAX(Date) AS MaxDate

RESIDENT [Your table name];

2. Indirect by creating a table with all dates that exists in the field Date from your table with the date: [Your table name].

LOAD

MIN(Date) AS MinDate,

MAX(Date) AS MaxDate;LOAD

       FieldValue('date', RecNo()) as datefield

AutoGenerate FieldValueCount('date');

srikantj
Contributor

Re: calendar date

LOAD
date(DateMin + IterNo()) as [date]        
WHILE DateMin + IterNo() <= DateMax     
;

LOAD
min(datefield)-1 as DateMin
,
max(datefield) as DateMax
;

LOAD
FieldValue('date', RecNo()) as datefield
AutoGenerate FieldValueCount('date');

This is the script he used to generate calendar dates

What my question is why can’t we use just these lines to generaye calendar dates

LOAD
date(DateMin + IterNo()) as [date]         // Link Field
WHILE DateMin + IterNo() <= DateMax     
;

LOAD
min(datefield)-1 as DateMin
,
max(datefield) as DateMax
;

Why we need to use these 2 lines. I don’t think we need these 2 lines

LOAD
FieldValue('date', RecNo()) as datefield
AutoGenerate FieldValueCount('date');