12 Replies Latest reply: Dec 20, 2017 1:29 AM by Luis Madriz RSS

    [Help] I need help for make the create table

    정훈 최

      Please help me.

      I am beginner  so, i need your help please

      Untitled.jpg

       

      Ceritification TableName TableI want to create table or pivot in qlik
      NamePositionCertificationNameDetermine how many certification by Name as like count-ifs in Excel
      ACA01>2>3
      APGYB1332
      ASGYC
      BSFYD
      BPFYE
      BCYF
      BPGG
      BSGYH
      CPGYI
      CSGY
      DSFY
      DPFY
      FCY
      GPGY
      HPGY
      HSGY
      HCY
      HSF
      ISFY
      IPF
        • Re: [Help] I need help for make the create table
          Luis Madriz

          Hi 정훈 최

           

          I don't this this was a very easy exercise for me. I'm calling the artillery: stalwar1

           

          I think I did it but I'm not sure if it is the right way. At least the numbers match. I struggled the most with Name 'E' as there are no certifications for it so it was null but you needed 0 in the final table at the bottom...

           

          I hope there is a simple way

          Untitled.png

           

          This is the Dimension (Row) for the pivot table although it's not really required:

          ='Summary'

           

          This is the Dimension (Column) for the pivot table

          =IF(ISNULL(AGGR(COUNT({<Certification={Y}>} Name),Name)),'0',

          IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 1 ,'1',

          IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 2 ,'2','3+')))

           

          And this is the Measure:

          Count((AGGR(COUNT(Name),Name)))

           

          This is what I used to load data:

           

          LOAD * INLINE [

          Name,Position,Certification

          A,C,

          A,PG,Y

          A,SG,Y

          B,SF,Y

          B,PF,Y

          B,C,Y

          B,PG,

          B,SG,Y

          C,PG,Y

          C,SG,Y

          D,SF,Y

          D,PF,Y

          F,C,Y

          G,PG,Y

          H,PG,Y

          H,SG,Y

          H,C,Y

          H,SF,

          I,SF,Y

          I,PF,

          ];

           

          LOAD * INLINE [

          Name

          A

          B

          C

          D

          E

          F

          G

          H

          I

          ];

          • Re: [Help] I need help for make the create table
            Mariusz Kumański

            I'm not sure but I think these no name that doesn't have certification.My solution:

             

            book7.png

             

             

             

            =Count(DISTINCT{<count = {0}>}Name)

            =Count(DISTINCT{<count = {1}>}Name)

            =Count(DISTINCT{<count = {2}>}Name)

            =Count(DISTINCT{<count = {">=3"} >}Name)

             

            data:

            LOAD

            Name,

            Position,

            Certification,

            if(isNull(Certification), 0, 1) as value

            FROM [C:\Temp\Book7.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1);



            Left Join

            LOAD

            Name,

            sum(value) as count

            Resident

            data

            Group by Name;

            • Re: [Help] I need help for make the create table
              Luis Madriz

              Thanks Sunny and Mariusz for your replies,

               

              I tried to let this go but I couldn't

               

              Mariusz, I like your solution but I'm just assuming this is required in the front end

              Sunny, I imagine you meant =2 instead of >2

               

              I don't think we've got it right yet! The results seem fine based on the current data but if there were no Names with the number of certifications needed (0,1,2,3+) then the dimension wouldn't show. For example if E didn't exist we wouldn't have the category with '0'.

               

              We need a fixed Dimension with this:

              =ValueList('0','1','2','3+')

              That way, regardless of the data there will always be those 4 categories. I mean please don't get me wrong, it's not that it was explicitly said on this post, I'm just making it a bit more fun!

               

              And this is the measure that I could come up with:

              =IF(ValueList('0','1','2','3+')='0',COUNT(DISTINCT Name) - COUNT(DISTINCT {<Certification={Y}>} Name),

              IF(ValueList('0','1','2','3+')='1',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =1,1)),

              IF(ValueList('0','1','2','3+')='2',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =2,1)),

                                                  SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) >2,1)))))

               

              I still struggled to get the first one where certification was null so I had to work around it

              Untitled.png

               

              This is what would happen if we didn't have E for example:

              Untitled.png

               

               

              And this is what would happen if there were no Names with 3 or more for example:

              Untitled.png

               

              Fun stuff!

               

              Cheers,

               

              Luis