13 Replies Latest reply: Jan 21, 2014 1:01 PM by Dinesh Reddy Seelam RSS

    Min and Max Dates

    Dinesh Reddy Seelam

      Hello everyone,

       

      I need a help!

       

      I Have a table with 4 different date fields with different dates it has warranty start date, end date and contract start, end dates

      so total of 4 dates I need Max of Warranty End(marked in red) and Min Contract Start (marked in red) which is possible through resident load

      but when ever I reload it I am getting duplicates in my straight table when I analyse data came to know it is pulling max warranty end and min contract start but pulling all the dates from warranty start and contract end which is wrong

       

      Output should should be only max warranty end date with corresponding warranty start and min contract start date with corresponding contract end

      where I don't need min or max for warranty start and contract end I need only corresponding dates.

      Example:

      IDWARRANTY STARTWARRANTY ENDCONTRACT STARTCONTRACT ENDCOUNT
      101/01/201201/01/201301/02/201301/02/20141
      201/01/201201/01/201301/01/201301/02/20141
      301/02/201201/01/201310/02/201201/02/20140
      4----1
      501/02/201201/02/201305/05/201401/09/20171

       

      Guys please help me thanks  in advance

      This is what I am using

       

      MaxWarranty_10:

      LOAD

      ID,

      Max(Warrantydate) AS [WARR END]

      RESIDENT Warrantys

      Group BY ID;


      MinContract:

      LOAD

      SID,

      Min([Contracts.START_DATE]) AS [CONTRACT START]

      Resident Warrantys

      Group BY SID;

        • Re: Min and Max Dates
          Michael Gardner

          Have you tried LOAD DISTINCT on the two resident tables?

          • Re: Min and Max Dates

            I don't see the link to join MaxWarranty_10 and MinContract tables. If there's none, by displaying it all together you get a cartesian product.

            I'd see something like this:

             

            MinMaxTable:

            LOAD

            ID,

            Max(Warrantydate) AS [WARR END]

            RESIDENT Warrantys

            Group BY ID;

             

            Join(MinMaxTable)

            LOAD

            SID as ID,

            Min([Contracts.START_DATE]) AS [CONTRACT START]

            Resident Warrantys

            Group BY SID;

             

            And so on for the other fields.

             

            Hope this helps.

              • Re: Min and Max Dates
                Dinesh Reddy Seelam

                Hello Pierre,

                 

                There is a link it is a resident load so there is no link between residents there is b/w main tables by using ID as an primary key

                  • Re: Min and Max Dates

                    OK. Then it depends how SID and ID match in your data, if there's always 1 ID for 1 SID, links to null entries in the data structure etc. Maybe you can try:

                     

                    AggregateTable:

                    LOAD

                    ID, SID,

                    Max(Warrantydate) AS [WARR END],

                    Min([Contracts.START_DATE]) AS [CONTRACT START]

                    Resident Warrantys

                    Group BY ID, SID;

                     

                    and see how it looks. Or make it through a pivot chart.

                      • Re: Min and Max Dates
                        Dinesh Reddy Seelam

                        Hello pierrie,

                         

                        I attached a Sample QVW check it out

                         

                        Output should be 1st criteria MAX WARR END DATE  following MIN CONTRACT START DATE and corresponding dates

                        not supposed to get any duplicates but in the sample u can see some duplicates

                         

                        Results expecting

                         

                        IDWarranty.CONTRACT_TYPEWARR STARTWARR ENDCON STARTCON ENDContracts.CONTRACT_TYPE
                        1-02/04/201201/04/201301/10/201104/07/2014FS
                        2EW01/01/201330/06/201311/03/201010/03/2014POP
                        3EW--01/04/201031/03/2014POP
                        4WS11/03/201310/03/2014---
                        5EW01/01/201330/06/201327/11/201226/11/2016POP
                        6EW21/01/201320/07/201301/01/201331/12/2016POP
                        7WS13/06/201212/06/201301/01/201331/12/2016POP
                        8---12/03/201312/03/2013POP
                        9-27/06/201226/06/201309/07/201230/06/2014PP
                        10-26/12/201225/12/201315/06/201215/06/2012FS
                  • Re: Min and Max Dates
                    Srikanth P

                    As per your statements, there is no problem. I am suspecting this data model problem

                    Please post some sample source data.

                    • Re: Min and Max Dates
                      Dinesh Reddy Seelam

                      Just attached a SAMPLE QVW

                       

                      Plzzz Check it out

                       

                      Requirement

                      Output should be 1st criteria MAX WARR END DATE  following MIN CONTRACT START DATE and corresponding dates

                      not supposed to get any duplicates but in the sample u can see some duplicates

                       

                      Results expecting

                       

                      IDWarranty.CONTRACT_TYPEWARR STARTWARR ENDCON STARTCON ENDContracts.CONTRACT_TYPE
                      1-02/04/201201/04/201301/10/201104/07/2014FS
                      2EW01/01/201330/06/201311/03/201010/03/2014POP
                      3EW--01/04/201031/03/2014POP
                      4WS11/03/201310/03/2014---
                      5EW01/01/201330/06/201327/11/201226/11/2016POP
                      6EW21/01/201320/07/201301/01/201331/12/2016POP
                      7WS13/06/201212/06/201301/01/201331/12/2016POP
                      8---12/03/201312/03/2013POP
                      9-27/06/201226/06/201309/07/201230/06/2014PP
                      10-26/12/201225/12/201315/06/201215/06/2012FS
                        • Re: Re: Min and Max Dates

                          Hi,

                           

                          See attached.

                           

                          1st problem: you need to calculate min and max dates, then join corresponding data, but some of those min and max are nulls, and joining null on null doesn't work. Nulls can be replaced by 1/1/1900 for example. Best time to do it is during the original load of Warranty table.

                           

                          2nd problem: if, in the resident table, there are only fields existing in Warranty table, by displaying fields of Warranty and resident table together in the same view, you'll get duplicates ignoring nulls when possible (I guess, by default, it's an outer join, not a left join).

                          In attached, 2 ways to solve this:

                          1) Make 2 fields for both Min and Max in resident table: one common with Warranty, will make the join, the other (same value) named another way, will show nulls when it's not data you want. The data corresponding to min and max is filtered in the table view by not displaying lines where 2nd field is null.

                          2) Make min and max in resident table named differently than in Warranty, then left joining the other fields, all with a new name.

                           

                          Hope this helps. If you want to show null dates instead of 1/1/1900, it can be done by adding new fields.