5 Replies Latest reply: Apr 17, 2013 8:33 AM by Kevin Flynn RSS

    Display one single record

    Abs Sheoran

      Hi

      I have a table with columns A, B, C, D, E, F and G.

      Now I want to display just one record for specific value in A in such a way other filters on the same sheet do not work on this.

        • Re: Display one single record

          That's a tough one. I've had similar-sounding problems and had to resort to lengthy set expressions. Can you give a little more detail as to what the situation is?

            • Re: Display one single record
              Abs Sheoran

              Sure,

               

              I have table (excel sheet uploaded in Qlikview) that contains many data fields.

              This problem focuses on few data fields - A,B,C,D and E.

               

              Column A is basically a scenario column (where value 1 is default scenario value) and other columns are regular number data fields.

              Sample data looks like this:

               

              ABCDE
              17120.671
              1250.33
              1970.494
              1970.735
              2990.23
              2150.313
              24170.552
              27130.663
              32170.241
              3960.513
              3720.952
              31120.482

               

               

              There will be a scenario drop down list that contains different column A values, in this example it will be 1,2,3 where 1 is default value.

              This drop down list will act as a filter and we will get all the values for the specific scenario that we select so far its easy.

              But for the purpose of comparing different scenario's I need to display default scneario values that do not change with filter selection i.e. default scenario (Column A value =1) should always be displayed irrespective of scenario filter we apply.

              Please find the QlikView file.

                • Re: Display one single record

                  That's actually relatively simple! Just replace your sum(B) with Sum({<A={1}>}B), sum(C) with =Sum({<A={1}>}C), etc. The Qlikview help tab has an excellent page on Set Analysis that I recommend reading for similar problems.

                    • Re: Display one single record
                      Abs Sheoran

                      Hi

                      Will it be possible if you can explain me the logic behind this expression.

                      thanks

                        • Re: Display one single record

                          This is pretty basic set analysis. Again, I can't stress strongly enough how good an idea it is to click 'help' and type 'Set Analysis' and then read the help info that Qlikview provides.

                           

                          Basically, the outer {} is a set statement. It is telling Qlikview to 'compute this equation where the statement inside the {} is true'. <> is a subset statement. It allows you to chain together multiple set statements. The most common example of this is Sum({$<A={1}>}C), which is 'The sum of all C where A = 1 and '$', which means 'current selections apply'.

                           

                          The inner {} is another set statement, but in this case it's the possible values that A can take. If you wanted to take the sum of A=1 and A=2, it'd be Sum({$<A={1,2}>}C). Hope this helps!