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

topic Re: how to get minimum and maximum date from multiple table in QlikView https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341990#M617373 <HTML><HEAD></HEAD><BODY><P>Dates:</P><P>LOAD distinct <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table1;</SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table2;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table3;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date ,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table4;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date ,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table5;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax:</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">LOAD min(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date) as MinDate,</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">          <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">max(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">) as MaxDate</SPAN></SPAN></P><P>Resident <SPAN style="font-size: 13.3333px;">Dates;</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">let vMinDate = peek('<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinDate</SPAN>',0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax</SPAN>');</SPAN></P><P><SPAN style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">let vMaxDate = peek('<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MaxDate</SPAN></SPAN><SPAN style="font-size: 13.3333px;">',0,'</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax</SPAN><SPAN style="font-size: 13.3333px;">');</SPAN></SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">drop table Dates;</SPAN></P></BODY></HTML> Thu, 22 Jun 2017 13:17:24 GMT Kushal_Chawda 2017-06-22T13:17:24Z how to get minimum and maximum date from multiple table https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341989#M617372 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>I have 1 requirement where i have below given tables:</P><UL><LI>Contact_F</LI><LI>Contact_Details</LI><LI> Contact_Address</LI><LI>Contact_Emails</LI><LI>Contact_References</LI></UL><P></P><P>all tale having field called created_date and modified_date.</P><P></P><P>now i want to get Minimum created date from comparing to all 5 tables and maximum modified_date comparing all 5 table.</P><P>For example:</P><P> <SPAN style="font-size: 13.3333px;">Contact_F</SPAN></P><P><SPAN style="font-size: 13.3333px;">-----------------</SPAN></P><P><SPAN style="font-size: 13.3333px;">Cotact WID , Created_date,Modified_Date</SPAN></P><P><SPAN style="font-size: 13.3333px;">RMG01       , 2017/06/05, 2017/06/21</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"> <SPAN style="font-size: 13.3333px;">Contact_Details</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">-----------------</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">Cotact WID , Created_date,Modified_Date</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">RMG01       , 2017/06/05, 2017/06/22</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"> <SPAN style="font-size: 13.3333px;">Contact_Address</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">-----------------</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">Cotact WID , Created_date,Modified_Date</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">RMG01       , 2017/06/04, 2017/06/21</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P>Contact_Emails</P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">-----------------</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">Cotact WID , Created_date,Modified_Date</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">RMG01       , 2017/06/02, 2017/06/21</SPAN></P><P><SPAN style="font-size: 10pt;"><BR /></SPAN></P><P><SPAN style="font-size: 10pt;">Contact_References</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">-----------------</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">Cotact WID , Created_date,Modified_Date</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">RMG01       , 2017/06/02, 2017/06/18</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">As per above tables the minimum created_date is <SPAN style="font-size: 13.3333px;">2017/06/02 and maximum modified_date is <SPAN style="font-size: 13.3333px;"> 2017/06/22.</SPAN></SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">please suggets how to get minimum created date and maximum Modified date from above tables .</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">later i need to left join all tables with Contact_f.<BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P>this minimum created_date and maximum Modified_date will further use for query filter in Qlik script.</P><P></P><P></P><P>Please suggest </P></BODY></HTML> Thu, 22 Jun 2017 12:58:57 GMT https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341989#M617372 qliklearnervir 2017-06-22T12:58:57Z Re: how to get minimum and maximum date from multiple table https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341990#M617373 <HTML><HEAD></HEAD><BODY><P>Dates:</P><P>LOAD distinct <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table1;</SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table2;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table3;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date ,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table4;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">concatenate(<SPAN style="font-size: 13.3333px;">Dates</SPAN></SPAN><SPAN style="font-size: 13.3333px;">)</SPAN></P><P style="font-size: 13.3333px;">LOAD <SPAN style="font-size: 13.3333px;"> distinct </SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date ,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">FROM table5;</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax:</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">LOAD min(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">created_date) as MinDate,</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">          <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">max(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13.3333px;">Modified_Date</SPAN></SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">) as MaxDate</SPAN></SPAN></P><P>Resident <SPAN style="font-size: 13.3333px;">Dates;</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">let vMinDate = peek('<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinDate</SPAN>',0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax</SPAN>');</SPAN></P><P><SPAN style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">let vMaxDate = peek('<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MaxDate</SPAN></SPAN><SPAN style="font-size: 13.3333px;">',0,'</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MinMax</SPAN><SPAN style="font-size: 13.3333px;">');</SPAN></SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">drop table Dates;</SPAN></P></BODY></HTML> Thu, 22 Jun 2017 13:17:24 GMT https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341990#M617373 Kushal_Chawda 2017-06-22T13:17:24Z Re: how to get minimum and maximum date from multiple table https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341991#M617374 <HTML><HEAD></HEAD><BODY><P>maybe you can read from your field instead from your tables, an example of which can be found e.g. here:</P><P></P><P><A href="https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/" title="https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/">https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/</A></P><P></P><P>regards</P><P></P><P>Marco</P></BODY></HTML> Mon, 26 Jun 2017 23:42:20 GMT https://community.qlik.com/t5/QlikView/how-to-get-minimum-and-maximum-date-from-multiple-table/m-p/1341991#M617374 MarcoWedel 2017-06-26T23:42:20Z