topic Re: calendar date in QlikView

calendar date

srikantj — Wed, 25 Nov 2020 16:16:04 GMT

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');

Re: calendar date

prma7799 — Fri, 01 Dec 2017 10:09:00 GMT

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');

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 10:13:43 GMT

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.

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:15:22 GMT

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

Min(date) is enough to find minimum date

Same goes for maximum

Re: calendar date

prma7799 — Fri, 01 Dec 2017 10:20:24 GMT

You can get min or max like this also

LET vDataMax= fLOOR(Today());

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

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:22:39 GMT

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

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:25:18 GMT

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

I just want to know why he scripted like this

Re: calendar date

prma7799 — Fri, 01 Dec 2017 10:26:35 GMT

Temp:

LOAD

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

AutoGenerate FieldValueCount('date');

LOAD

min(datefield) as DateMin

,max(datefield) as DateMax

resident Temp;

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 10:28:23 GMT

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');

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:35:59 GMT

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');

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:38:58 GMT

Yes. you are right. Is there any special purpose to create table with all dates rather than just using your option

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:39:20 GMT

i mean your first option

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 10:44:24 GMT

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];

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 10:46:46 GMT

Nope, I don't think so. If there is a reason then it is performance wise. Maybe on large dataset MIN / MAX on the resident table is slower the just creating a list of all dates that exists in your source table. In that case you only have one field in your table instead of all the other fields. Less columns are faster than a lot of columns. And only loading unique values creater lesser rows and lesser rows are faster then a lot of rows.

If you're working with a small dataset don't worry. If you have a large dataset. Try both ways and check the time needed for the reload.

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:51:02 GMT

I have one more doubt. If i reloaded qvw with just these 2 lines

LOAD

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

AutoGenerate FieldValueCount('date');

I got result like this.

why datefield has all ? marks instead of dataset

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 10:54:18 GMT

I suggest you should check the formatting of the field: [datefield]. Maybe the format isn't correct and it shows a ?. You could also check the table viewer. there you can see the values just as they are (without any format).

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:55:56 GMT

Yes, you are right. Got it thank you.

Re: calendar date

srikantj — Fri, 01 Dec 2017 10:58:35 GMT

Yes formatting is not right. Thank you

Thanks for your patience and detailed explanation

A.M. van Keep

Re: calendar date

avkeep01 — Fri, 01 Dec 2017 11:00:53 GMT

No Problem. You're welcome.