13 Replies Latest reply: Jul 28, 2017 6:04 AM by Agrim Sharma RSS

    If condition

    Agrim Sharma

      Hi,

      >I want to have count “Employee” who have all values equal to ‘0’ from Jan-Dec as ‘Allocated’

      >Count “Employee” who have all values less than ‘0’ from Jan-Dec as ‘Over allocated’

      And

      >Count “Employee” who have all values Greater than ‘0’ from Jan-Dec as ‘Available’

       

       

      I applied this formula but am not getting desired results

       

      = IF((Value3) = '0' , 'Allocated', IF((Value3) < '0' , 'Over Allocated', if((Value3) > '0' , 'Available' )))

       

      Below is my data sample:-

       

      EmployeeJanFebMarAprMayJunJulAugSepOctNovDec
      Adeline HEUSELE222110171819-1-223232122
      Alexandre DHELLEMME2322-11120-123242021
      Alexandre FAVRE222219161416-4-518201816
      Amandine SCOUMAQUE212123212022-1023241712
      Amarish THAKAR2220221891010897124
      Anthony LEMAI2121-15612121114152223
      Antoine GOUVERNEUR2121242219210223232120

       

      And desired result is as follows:-

      Allocated=0(no Employee has all values '0' from Jan-Dec)

      Over Allocated=5(Adeline HEUSELE, Alexandre DHELLEMME, Alexandre FAVRE, Amandine SCOUMAQUE, Anthony LEMAI)

      Available=2(Amarish THAKAR, Antoine GOUVERNEUR)

        • Re: If condition
          Avinash R

          Could you share the sample data set for this ? how is the data jan , feb are all coming from different columns are in the same columns ..share the sample data will help you

            • Re: If condition
              Agrim Sharma

              Hi Avinash,

               

              Thanks for reply, attached is my sample data.

                • Re: If condition
                  Avinash R

                  Find the attachment for the solution

                    • Re: If condition
                      Agrim Sharma

                      Hi Avinash,

                       

                      i am using Qlik sense, can you send me the formula in writing here?

                      regards

                        • Re: If condition
                          Avinash R

                          Temp:

                          LOAD Employee,

                               Jan,

                               Feb,

                               Mar,

                               Apr,

                               May,

                               Jun,

                               Jul,

                               Aug,

                               Sep,

                               Oct,

                               Nov,

                               Dec

                          FROM

                          \\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

                          (ooxml, embedded labels, table is Sheet1);

                           

                           

                          CrossTable(Month,value)

                          LOAD Employee,

                               Jan,

                               Feb,

                               Mar,

                               Apr,

                               May,

                               Jun,

                               Jul,

                               Aug,

                               Sep,

                               Oct,

                               Nov,

                               Dec

                          FROM

                          \\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

                          (ooxml, embedded labels, table is Sheet1);

                           

                          Expression :

                          if(count({<value={0}>}value)=12,'Allocated',if(count({<value={"<0"}>}value)>=1,'Over Allocated',

                          if(count({<value={">0"}>}value)>1,'Avilable')

                          ))

                           

                           

                          c1.png

                  • Re: If condition
                    Andrew Walker

                    Hi Agrim,

                     

                    You say:

                    >Count “Employee” who have all values less than ‘0’ from Jan-Dec as ‘Over allocated’


                    but no employees have all values less than zero, did you mean


                    >Count “Employee” who have some values less than ‘0’ from Jan-Dec as ‘Over allocated’


                    ?


                    Regards


                    Andrew

                      • Re: If condition
                        Agrim Sharma

                        yes exactly this i want,

                         

                        i want to check from jan to dec for all employee, if any value less than 0 from jan to dec i want it as over allocated employee but if all value greater than 0 than employee should be Available and if all the from jan to dec is 0 than it should be allocated

                      • Re: If condition
                        Andrey Khoronenko

                        Hi,

                         

                        May be like this?

                         

                        Table1:

                        LOAD Employee,

                            Jan,

                            Feb,

                            Mar,

                            Apr,

                            May,

                            Jun,

                            Jul,

                            Aug,

                            Sep,

                            Oct,

                            Nov,

                            Dec

                        FROM

                        [https://community.qlik.com/thread/269289?sr=inbox&ru=232295]

                        (html, codepage is 1251, embedded labels, table is @1);

                         

                        Table2:

                        CrossTable(Month, Value)

                        LOAD*

                        Resident Table1;

                         

                        //check the first condition

                        Table3:

                        LOAD

                        Employee,

                        If(Count(Value)=0, 'Allocated') as Allocated

                        Resident Table2

                        Where Value<>0

                        Group By Employee;

                         

                        //check the second condition

                        Table4:

                        LOAD

                        Employee,

                        If(Count(Value)>0, 'Over Allocated') as OverAllocated

                        Resident Table2

                        Where Value<0

                        Group By Employee;

                         

                        //check the third condition

                        Table5:

                        LOAD

                        Employee,

                        If(Count(Value)=12, 'Available') as Available

                        Resident Table2

                        Where Value>=0

                        Group By Employee;

                         

                        //attached the results to the main table

                        Left Join (Table1)

                        LOAD*

                        Resident Table3;

                         

                        Left Join (Table1)

                        LOAD*

                        Resident Table4;

                         

                        Left Join (Table1)

                        LOAD*

                        Resident Table5;

                         

                        DROP Tables Table2, Table3, Table4, Table5;

                         

                        Result

                        1.jpg

                         

                        Example at attached file.

                         

                        Regards,

                        Andrey

                          • Re: If condition
                            Agrim Sharma

                            my script is like this, your logic is right, how can i use that in this script?


                            Temp3:

                            CrossTable( Month, Value3,1)

                            LOAD

                            "Employee",

                            Jan,

                                 Feb,

                                 Mar,

                                 Apr,

                                 May,

                                 Jun,

                                 Jul,

                                 Aug,

                                 Sep,

                                 Oct,

                                 Nov,

                                 Dec

                                

                            FROM [lib://rp/OCC_Resource Planning.xlsx]

                            (ooxml, embedded labels, table is Availability);

                             

                            MappingTable4:

                            Mapping

                            LOAD

                            [Employee]&Month(Date#(Month, 'MMM')),

                              Value3

                             

                            Resident Temp3;


                            • Re: If condition
                              Agrim Sharma

                              Thanks a lot Andrey, can you plese tell me why you used '12' for If(Count(Value)=12, 'Available') as Available ?

                            • Re: If condition
                              Andrew Walker

                              Hi Agrim,

                               

                              Try:

                              Data:

                              CrossTable(Month,Value)

                              LOAD * INLINE [

                                  Employee, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

                                  Adeline HEUSELE, 22, 21, 10, 17, 18, 19, -1, -2, 23, 23, 21, 22

                                  Alexandre DHELLEMME, 23, 22, -1, 1, 1, 2, 0, -1, 23, 24, 20, 21

                                  Alexandre FAVRE, 22, 22, 19, 16, 14, 16, -4, -5, 18, 20, 18, 16

                                  Amandine SCOUMAQUE, 21, 21, 23, 21, 20, 22, -1, 0, 23, 24, 17, 12

                                  Amarish THAKAR, 22, 20, 22, 18, 9, 10, 10, 8, 9, 7, 12, 4

                                  Anthony LEMAI, 21, 21, -1, 5, 6, 12, 12, 11, 14, 15, 22, 23

                                  Antoine GOUVERNEUR, 21, 21, 24, 22, 19, 21, 0, 2, 23, 23, 21, 20

                              ];

                               

                              then

                               

                              Count Allocated =count(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee)

                              Count Over Allocated =count(DISTINCT {$<Value = {"<0"}>} Employee)

                              Count Available =count(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee)

                               

                              Allocated Employees =concat(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee,', ')

                              Over Allocated Employees  =concat(DISTINCT {$<Value = {"<0"}>} Employee,', ')

                              Available Employees  =concat(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee,', ')

                               

                              Regards

                              Andrew

                                • Re: If condition
                                  Agrim Sharma

                                  this is my script, can you please help on this

                                   

                                  Temp3:

                                  CrossTable( Month, Value3,1)

                                  LOAD

                                  "Employee",

                                  Jan,

                                       Feb,

                                       Mar,

                                       Apr,

                                       May,

                                       Jun,

                                       Jul,

                                       Aug,

                                       Sep,

                                       Oct,

                                       Nov,

                                       Dec

                                      

                                  FROM [lib://rp/OCC_Resource Planning v1.5a.xlsx]

                                  (ooxml, embedded labels, table is Availability);

                                   

                                  MappingTable4:

                                  Mapping

                                  LOAD

                                  [Employee]&Month(Date#(Month, 'MMM')),

                                    Value3

                                   

                                  Resident Temp3;

                                    • Re: If condition
                                      Andrew Walker

                                      Hi Agrim,

                                      Using your sample spreadsheet you only need this script:

                                       

                                      Data:

                                      CrossTable(Month,Value)

                                      LOAD *

                                      FROM

                                      Sample.xlsx

                                      (ooxml, embedded labels, table is Sheet1);

                                       

                                      Then these expressions in the front end:

                                      Count Allocated =count(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee)

                                      Count Over Allocated =count(DISTINCT {$<Value = {"<0"}>} Employee)

                                      Count Available =count(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee)

                                       

                                      Allocated Employees =concat(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee,', ')

                                      Over Allocated Employees  =concat(DISTINCT {$<Value = {"<0"}>} Employee,', ')

                                      Available Employees  =concat(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee,', ')

                                       

                                      Regards

                                       

                                      Andrew