Min Max Date function

[tamilarasu]

Hi,

I am working on a file where i want to show the data based on date, month and year wise. I want to fetch the date from existing tables but i am not sure how to do that. I have tried the below code suggested by someone in forum, but it's not working as expected. Can anyone see the below attached file and let me know whether my data model is okay to fulfill the requirement. If not, please guide.

MaxMinDates:

LOAD Max(Datetmp) as MaxDate,

    Min(Datetmp) as MinDate;

LOAD FieldValue('%Date', IterNo()) as Datetmp

AutoGenerate 1

While IterNo()<=FieldValueCount('%Date');

LET vMinDate=FieldValue('MinDate', 1);

LET vMaxDate=FieldValue('MaxDate', 1);

!Many thanks in advance.

[jagan]

Hi,

I am not sure what you are trying to do, If you are trying to get the Min and Max dates then use below script otherwise explain what you are trying to do.

Temp:

LOAD

*

INLINE [

%Date

1/1/2015

1/2/2015

1/3/2015];

MaxMinDates:

LOAD Max([%Date]) as MaxDate,

    Min([%Date]) as MinDate

Resident Temp;

LET vMinDate=FieldValue('MinDate', 1);

LET vMaxDate=FieldValue('MaxDate', 1);

DROP TABLE MaxMinDates;

Regards,

Jagan.

[ToniKautto]

I think you might have a logical problem in your data model. All tables are associated over one common date field. This practically means that your application has one timeline. Is this what you want?

One time line means that your selection in the timeline will be affect on all data. For example if your data has production date and sales dates, a selection on sales data will relate to articles old on the selected date and the articles produced on the same date. It will not include production of the sold articles.

A common way to get around this is to introduce several calendars as described in the below blog post.

The Master Calendar

[ToniKautto]

I don't think that will be applicable in this case as the dates are spread in multiple tables.

[tamilarasu]

Hi Jagan,

I want to consolidate all date fields from the existing (4 tables) tables and want to store in a separate table. So that I can use it as data island and make charts based on the date. I want to show weekly, monthly, yearly report from the four tables.

[tamilarasu]

Hi Toni,

I just want to get Min and max dates from multiple tables and want to create a data island. As I am new to qlikview, I am not sure how to solve this. Is there any other method to get the desired result.

[danieloberbilli]

somthing like that?

MinMaxDate:

Load

'Tab1' as Source,

max(DateField) as MaxDatefield,

min(DateField) as MinDatefield

resident Tab1;

// concatenates automatically:

load

'Tab2' as Source,

max(DateField) as MaxDatefield,

min(DateField) as MinDatefield

resident Tab2;

[...]

Overall_MinMaxDate:

left join(MinMaxDate)

load

max(MaxDatefield) as TotalMaxDatefield,

min(MinDatefield) as TotalMinDatefield

resident MinMaxDate;

[ToniKautto · Accepted solution]

I think the script you are using should pick up the smallest and largest value in the %Date field. The problem in this case is however that your value are not actually dates, they are timestamps.

In the table viewer you can see that the values are decimal values. Dates are expected to be represented by integer value.

You can also see that if the %Date is presented in a list box there are several occurrences on the same date.

This means that you need to convert the %Date field values to Dates during the load form each source. For example according to the pattern below, where the timestamp (decimal value) to an integer by using the Floor() function

Prod_Details:
LOAD
  *,
  Date(Floor([%TmpDate])) AS [%Date];
SQL SELECT *,
    CmpDt as [%TmpDate],
    Day(CmpDt) as [Day],
    Month(CmpDt) as [Month],
    Year(CmpDt) as [Year]
FROM ProductivityDetails;

DROP Field [%TmpDate];

After making the dates into proper dates the list box of %Date should only show each date one time. And your max and min values will match the correct selection.

[tamilarasu]

!Hi Toni,

I have modified the data model but the variables are not populated. I can see the date format and it's proper. What could be the reason.?

Please see the attached file. ! !

[rwunderlich]

The problem is due to the Qualify * you have in effect earlier in the script. Your fields are getting qualified so the correct LET statements should be:

LET vMinDate=FieldValue('MaxMinDates.MinDate', 1);

LET vMaxDate=FieldValue('MaxMinDates.MaxDate', 1);

-Rob

http://masterssummit.com

http://robwunderlich.com