7 Replies Latest reply: Feb 16, 2015 1:11 PM by Drew Collins RSS

    Max Value based on a date

    Drew Collins

      Hi,

       

      I know the answer to this is simple and I am just having a brain-fart. I have a table with site names, dates, and scores. For each site name, I need to pull the scores based on the latest date.

       

      Site NameDateProcessProcess A ScoreProcess B Score
      Boston1/10/15A4
      Boston9/8/14B4
      Chicago11/11/14B5
      Chicago8/1/14A,B24
      Chicago2/2/14B2
      Memphis11/1/14A4
      New York12/6/14B3
      New York8/5/14A,B44
      Philadelphia1/1/15A4
      Philadelphia10/10/14B4
      Philadelphia4/1/14A,B33

       

      I want to create a straight table that shows the most recent score based upon the process. In the example below, last time we went to Philadelphia and looked at Process A was 1/1/15 and the score was 4. Last time we went to Philadelphia and looked at Process B was 10/10/14 and the score was 4.

       

      Site NameProcess A ScoreProcess B Score
      Boston44
      Chicago25
      Memphis4
      New York43
      Philadelphia44

       

      I have tired the FirstSortedValue() function but end up getting null values for certain sites and I cannot figure out why. It's driving me crazy!

       

      Thanks in advance,

       

      Drew

        • Re: Max Value based on a date

          I don't know how you are planning to handle Process A,B case but try adding set analysis to your formula.

           

          For A

          FirstSortedValue({$ <Process = {'A'}> }[Process A Score],-Date)

           

          For B

          FirstSortedValue({$ <Process = {'B'}> }[Process B Score],-Date)

          • Re: Max Value based on a date
            Anosh Nathaniel

            Hi Drew,

             

            To use Erdal's solution you need to load your table in such a format where 'Process' field doesn't have any comma separated values. Add additional records for second 'Process' values. such like shown below:

                                                                                                                                                      

            Site NameDateProcessProcess A ScoreProcess B Score
            Boston1/10/2015A4
            Boston9/8/2014B 4
            Chicago11/11/2014B 5
            Chicago8/1/2014A2
            Chicago8/1/2014B 4
            Chicago2/2/2014B 2
            Memphis11/1/2014A4
            New York12/6/2014B 3
            New York8/5/2014A44
            New York8/5/2014B 4
            Philadelphia1/1/2015A4
            Philadelphia10/10/2014B 4
            Philadelphia4/1/2014A3
            Philadelphia4/1/2014B 3

             

            You can do concatenate load of the same table two times like:

            Load SiteName, Date, Left(Process,1), [Process A Score], if(index(Process,','),null(),[Process B Score]) as [Process B Score]

            from <TableName>;

            concatenate

            Load

            SiteName, Date, Right(Process,1), '' as [Process A Score], [Process B Score],

            from <TableName> where index(Process,',')>=1;

             

            There are other methods also to create above table like load A process once and then load B process etc. (Depends upon your requirement).

             

            One you get this table structure you can use Erdal's formula's in expression to get the desired results.

             

            Hope this helps,

            Anosh

            • Re: Max Value based on a date
              Michael Matern

              Hi,

               

              I would you recommend you to generate two field (for Process A & B score) which already contains the scores based on the latest date. You can do it as follows

               

               

              Temptabale:

               

              load ID,

              firstsortedvalue([Process A score],-Date) as Process_A_score_latest,

              firstsortedvalue([Process B score],-Date) as Process_B_score_latest,

              from

               

              \\Your.qvd (qvd)

              group by

              ID

               

               

               

              The generated fields you can join to the relevant table.


              BR

              Michael

                • Re: Max Value based on a date
                  Drew Collins

                  Thanks Michael. Your suggestion was where my brain was going. I am going to test it today and see how it works.

                    • Re: Max Value based on a date
                      Anosh Nathaniel

                      Hi Drew,

                      If you want to keep the final structure with site having process score in your datamodel and link it further with other tables, than it is a good idea to do it in datamodel otherwise i would suggest you to either go with Qlikranjan or my explained solution (depends upon the table structure you would like to keep in datamodel).

                       

                      You should incorporate business logic in data load script only when

                      1. You would like to keep the output (eg Aggregated table)  in datamodel and don't want the source data

                      2. There is no possible way to achieve it in UI other than doing it in load script;

                      if you keep only the aggregated table in datamodel, you would be eliminating the future need of source data.

                      if you keep both source and aggregated table in datamodel, you would be replicating the data in datamodel, which would increase the load time, size of app and memory footprint on server.

                       

                      Hope this help,

                      Anosh

                    • Re: Max Value based on a date
                      Drew Collins

                      Hi Michael,

                       

                      Using your solution, I still get null values.

                       

                         

                      SiteProcess AProcess BDate
                      Philadelphia441/1/2015
                      Philadelphia310/10/2014
                      Philadelphia34/1/2014

                       

                      With the above, I would expect to get

                       

                      Philadelphia 4 4

                       

                      However, I get the below:

                       

                        

                      SiteProcess A Score (Latest)Process B Score (Latest)
                      Philadelphia--

                      Any idea what causes the null values?