Comparing Values to a Dynamic Calendar

[#1]

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!

[Anonymous]

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

[#3]

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!

[Miguel_Angel_Baeyens · Accepted solution]

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

[#5]

Miguel,

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