3 Replies Latest reply: Apr 13, 2017 4:25 PM by omar bensalem RSS

    Find Row where somedate=Max(somedate) and deliveryType='foo'

    Justin Dallas

      Hello Everyone,

       

      So I am working on a chart where the only rows I want is showing either are the (earliest pickup date ) or (latest dropoff date).  In SQL, this is known as the greatest-n-per-query problem.  I'm not sure how to solve it in the Qlik world, but I feel like I need some combination of MAX,MIN and AGGR.  I've got a small image of what I am striving for, and I've marked the rows I don't want with an X.  My test data isn't complex and it looks like this.

       

      Branches:
      Load * inline
      [
        BranchId ,BranchName
        1,NorthBranch
        2,CentralBranch
        3,WesternBranch
      ];
      
      
      BranchDeliveries:
      Load * inline
      [
        BranchId ,DeliveryType,DeliveryDate,Goal
        1,'Pickup','1/1/2017'
        1,'DropOff','1/2/2017' 
        1,'DropOff','1/3/2017'
        1,'DropOff','1/4/2017'
        
        2,'Pickup','1/7/2017'
        2,'DropOff','1/8/2017'
        2,'DropOff','1/9/2017'
        
        3,'Pickup','1/7/2017'
        3,'DropOff','1/8/2017'
      ];
      

       

      Remove_1.PNG

      Any help is greatly appreciated.

        • Re: Find Row where somedate=Max(somedate) and deliveryType='foo'
          omar bensalem

          You can do it in sql then load what you want to load in Qlik.

           

          Qlik performs this way:

           

          load

          A,

          B

          SELECT

          A,

          B,

          C

          FROM SOURCE;

           

          The select part i reserved to the source; I mean, you can use the syntax you're using in SQL there.

           

          In your case, you can build your condition in the Select part using what you're used to have then load *.

           

          Exp:

           

          Load*

          select

          //do your sql queries here to select the fields you wanna select

          from source;

           

          You don't have to use Qlik ssyntasx

          Hope this helps !

            • Re: Find Row where somedate=Max(somedate) and deliveryType='foo'
              omar bensalem

              Now you want to do it with qlik in the script, here''s how you proceed:

              Branches:

              Load * inline

              [

                BranchId ,BranchName

                1,NorthBranch

                2,CentralBranch

                3,WesternBranch

              ];

               

               

              BranchDeliveries:

               

               

              Load * inline

              [

                BranchId ,DeliveryType,DeliveryDate

                1,Pickup,1/1/2017

                1,DropOff,1/2/2017

                1,DropOff,1/3/2017

                1,DropOff,1/4/2017

                

                2,Pickup,1/7/2017

                2,DropOff,1/8/2017

                2,DropOff,1/9/2017

                

                3,Pickup,1/7/2017

                3,DropOff,1/8/2017

              ];

               

              NewTable:

              load BranchId,DeliveryType, date(date#(DeliveryDate,'M/D/YYYY')) as Date

              Resident BranchDeliveries ;

              drop field DeliveryDate;

               

               

              drop Table BranchDeliveries;

               

              Final:

              load BranchId,DeliveryType, max(Date) as DateDeliv Resident NewTable

                where DeliveryType='DropOff'

               

               

              Group by BranchId,DeliveryType

              ;

               

              load BranchId,DeliveryType, min(Date) as DateDeliv Resident NewTable

                where DeliveryType='Pickup'

               

               

              Group by BranchId,DeliveryType

              ;

              drop Table NewTable;

               

               

              Capture.PNG

               

              see the attached app

            • Re: Find Row where somedate=Max(somedate) and deliveryType='foo'
              Craig Sutton

              A Straight Table with the following expression: =If(DeliveryType='Pickup', min(DeliveryDate), If(DeliveryType='DropOff', Max(DeliveryDate)))

              dimensions BranchName and DeliveryType