topic Re: Comparing Values to a Dynamic Calendar in QlikView

Comparing Values to a Dynamic Calendar

Tue, 05 Jul 2011 15:23:56 GMT

Greetings everyone!!!

I have te following scenario to solve in hands...

I'm trying to create a master calendar to control dates of three different tables. To do so, I need to calculate the MAXs and MINs of the dates of the three tables, and compare the results to get only one real MIN and MAX. With it I'll be able to create the calendar.

I tried nesting IFs to do so, but, with no success...

Can anyone shed some light on the matter?

If any further explanation is needed, please post and I'll provide it as quick as I can.

Thank you all in advance!

Comparing Values to a Dynamic Calendar

Anonymous — Tue, 05 Jul 2011 15:45:12 GMT

You Can create a tmp table with the min and max dates from the 3 tables and then get min and max from the tmp table. Henrik

Comparing Values to a Dynamic Calendar

Tue, 05 Jul 2011 17:18:36 GMT

Henrik,

The question is how to compare three dates to find minimum and maximum values. Here is the table with all MAXs and MINs.

MinMaxDates:

LOAD

MIN(Date1) AS MinDateTable1,

MAX(Date1) AS MaxDateTable1

RESIDENT

Table1;

CONCATENATE (MinMaxDate)

LOAD

MIN(Date2) AS MinDateTable2,

MAX(Date2) AS MaxDateTable2

RESIDENT

Table2;

CONCATENATE (MinMaxDate)

LOAD

MIN(Date3) AS MinDateTable3,

MAX(Date3) AS MaxDateTable3

RESIDENT

Table3;

Now, all MINs must be compared among themselves, to find real MIN. The same to the MAXs. The issue here, is how to do it (IF nested statements, Set Analysis...).

Thank you!

Re: Comparing Values to a Dynamic Calendar

Miguel_Angel_Baeyens — Tue, 05 Jul 2011 17:44:52 GMT

Hi,

What about this?

MinMaxDatesTemp: // tables are concatenated implicitly since they have the same number and name of fields
LOAD MIN(Date1) AS MinDate,
     MAX(Date1) AS MaxDate
RESIDENT Table1;
LOAD MIN(Date2) AS MinDate,
     MAX(Date2) AS MaxDate
RESIDENT Table2;
LOAD MIN(Date3) AS MinDate,
     MAX(Date3) AS MaxDate
RESIDENT Table3;
MinMaxDates: // get the max of the max and the min of the min
LOAD Min(MinDate) AS MinDateActual,
     Max(MaxDate) AS MaxDateActual
RESIDENT MinMaxDatesTemp,
DROP TABLE MinMaxDatesTemp;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Comparing Values to a Dynamic Calendar

Wed, 06 Jul 2011 12:47:39 GMT

Miguel,

NAILED IT!!! Thank you very much!!!