9 Replies Latest reply: Sep 30, 2016 12:50 PM by Noha Salem RSS

    getting a column result based on another column

    Noha Salem

      Hi all,

       

      I'm 20 days old in qlik sense and couldn't figure out this problem.

      so I have a chart table in which one of its column shows the max transaction date when a certain product is chosen by the filter .

      now in that same table I have a column that displays the quantity on hand, so i need that column to shoe the quantity on hand on the exact same max date date. currently it shows all the quantities on hand for that product.

      this how the table currently shows :

      pic 1.PNG

      and this is how I want it to show based on product selection:

      pic 2.PNG

      the script for the max date is Date (MAX([OnHand History Transaction.AsOfDate]))

       

      any ideas?

        • Re: getting a column result based on another column
          Matthew Sanchez

          You can use aggr to get the results you are looking for.  I've attached a QVD with data similar to what you are describing.

           

           

          Here is the data I used in my sample

           

           

           

           

           

          LOAD * INLINE [

              PART, DATE, DESC, VALUE, QTY

              A, 6/21/2016, PRODUCTA, 8, 10

              A, 6/22/2016, PRODUCTA, 8, 15

              A, 6/23/2016, PRODUCTA, 8, 14

              A, 6/24/2016, PRODUCTA, 8, 143

              A, 6/25/2016, PRODUCTA, 8, 144

              B, 6/21/2016, PRODUCTB, 8, 24

              B, 6/22/2016, PRODUCTB, 8, 25

              B, 6/23/2016, PRODUCTB, 8, 26

              C, 6/21/2016, PRODUCTC, 8, 345

              C, 6/22/2016, PRODUCTC, 8, 14

              C, 6/23/2016, PRODUCTC, 8, 1

              C, 6/24/2016, PRODUCTC, 8, 132

          ];

           

           

          I used the same formula you described to get max date and this for qty on hand

           

          SUM(If(Aggr(NODISTINCT MAX(DATE), PART) = DATE, QTY))

           

          here is the resulting table.

           

           

          Aggr sample.PNG

           

           

          I have attached the sample QVD.  I hope this gets to you quickly.  My responses still need to be moderated.

            • Re: getting a column result based on another column
              Matthew Sanchez

              Side note.  I did not reference the max date column in my final code but the same formula of MAX(DATE).  if your scenario is more complicated and you don't wish to repeat code I can provide some code that uses variables to create reusable code for these situations.

               

               

              Basically you create a variable with your max date code named vMAXDATE (or whatever you prefer)

               

              DATE(MAX(DATE))

               

               

              then your max date column code is

               

              $(vMAXDATE)

               

               

              and your qty on hand code is

               

              SUM(If(Aggr(NODISTINCT $(vMAXDATE), PART) = DATE, QTY)) 

                • Re: getting a column result based on another column
                  Noha Salem

                  Hi Matthew,

                   

                  thank you for your reply ...So I did create a variable max date $(vMAXDATE) and used your second formula

                  SUM(If(Aggr(NODISTINCT $(vMAXDATE), PART) = DATE, QTY))

                  and now its giving me 0 instead of dashes that's better but still not the result I am hoping now the problem I am having is maybe because that for the exact same product it has many dates with different values of quantity on hand and I want it to display the quantity on hand corresponding to the max date.  here is a table that shows an example of the entries inside the table :

                  pic 3.PNG

                   

                  so the maximum date is 27th of September with value of -4902 so that's the value I want to show .

                   

                  hope that clears the task

                  • Re: getting a column result based on another column
                    Noha Salem

                    thanks matthew

                     

                    I wasn't able to do it your way but I found an option in the quantity on hand column if it is made as dimension which gave me the same result:

                     

                    pic4.PNG

                    as you can see from the picture this how i made it

                  • Re: getting a column result based on another column
                    Noha Salem

                    that is what I want I don't know why I am getting zeros

                     

                    here how I wrote it

                     

                    Sum(If(Aggr(NODISTINCT $(MaxDate),([OnHand History Transaction.PartNumber])) = DATE, ([OnHand History Transaction.Total])))

                     

                    and even without the extra brackets

                    Sum(If(Aggr(NODISTINCT $(MaxDate),[OnHand History Transaction.PartNumber]) = DATE, [OnHand History Transaction.Total]))

                     

                    still got 0 as an answer