Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qliklearnervir
New Contributor III

how to get minimum and maximum date from multiple table

Hi,

I have 1 requirement where i have below given tables:

  • Contact_F
  • Contact_Details
  • Contact_Address
  • Contact_Emails
  • Contact_References

all tale having field called created_date and modified_date.

now i want to get Minimum created date from comparing to all 5 tables and maximum modified_date comparing all 5 table.

For example:

Contact_F

-----------------

Cotact WID , Created_date,Modified_Date

RMG01       , 2017/06/05, 2017/06/21


Contact_Details

-----------------

Cotact WID , Created_date,Modified_Date

RMG01       , 2017/06/05, 2017/06/22


Contact_Address

-----------------

Cotact WID , Created_date,Modified_Date

RMG01       , 2017/06/04, 2017/06/21


Contact_Emails

-----------------

Cotact WID , Created_date,Modified_Date

RMG01       , 2017/06/02, 2017/06/21


Contact_References

-----------------

Cotact WID , Created_date,Modified_Date

RMG01       , 2017/06/02, 2017/06/18


As per above tables the minimum created_date is 2017/06/02 and maximum modified_date is 2017/06/22.

please suggets how to get minimum created date and maximum Modified date from above tables .

later i need to left join all tables with Contact_f.


this minimum created_date and maximum Modified_date will further use for query filter in Qlik script.

Please suggest

2 Replies

Re: how to get minimum and maximum date from multiple table

Dates:

LOAD distinct created_date,Modified_Date

FROM table1;


concatenate(Dates)

LOAD distinct created_date,Modified_Date

FROM table2;


concatenate(Dates)

LOAD distinct created_date,Modified_Date

FROM table3;


concatenate(Dates)

LOAD distinct created_date ,Modified_Date

FROM table4;


concatenate(Dates)

LOAD distinct created_date ,Modified_Date

FROM table5;


MinMax:

LOAD min(created_date) as MinDate,

          max(Modified_Date) as MaxDate

Resident Dates;


let vMinDate = peek('MinDate',0,'MinMax');

let vMaxDate = peek('MaxDate',0,'MinMax');


drop table Dates;

Re: how to get minimum and maximum date from multiple table

maybe you can read from your field instead from your tables, an example of which can be found e.g. here:

https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

regards

Marco

Community Browser