13 Replies Latest reply: Feb 21, 2017 2:33 PM by Agrim Sharma RSS

    how to create a for loop?

    Agrim Sharma

      i want to get the sum of team, if i select an employee and his role = Architect = 0.50 and his teams are software=0.25 and hardware = 0.25

      same employee has half of its role as developer=0.50 and teams as website developer=0.25 and software developer = 0.25

      Table1 (Role)                                              

      emp     Architect   developer

      x            0.50         0.50

       

       

      Table2(Team)

      emp             software   Hardware     website developer     software developer

           x                  0.25             0.25               0.25                              0.25

       

                                                                                                                               

        • Re: how to create a for loop?
          Vineeth Pujari

          please post some sample data and expected out

            • Re: how to create a for loop?
              Agrim Sharma

              this is my sample data. and dashboard. i want to filter role-Architect/PM and team -Finance so head count will be 9.55 but when i choose one more filter from team-industrial then i want the head count sum to be 3.33 but i am getting wrong sum of head count as 14.70    

                • Re: how to create a for loop?
                  Vineeth Pujari

                  what sheet should I refer to? also what is your problem to be solved/ expected output

                    • Re: how to create a for loop?
                      Agrim Sharma

                      you should refer Emp vs Role & Emp vs Team,attached  this file has combination of Emp vs Role & Emp vs Team in single sheet to check output by selecting filters that i mentioned

                      • Re: how to create a for loop?
                        Agrim Sharma

                        Hi Vineeth,

                         

                        Did you found something?

                         

                        Thanks

                          • Re: how to create a for loop?
                            Vineeth Pujari

                            First Load Employee details and then Crosstable Load ROLE and FTE values, Your data model will look like below

                             

                            Datamodel.JPG

                             

                             

                             

                             

                            LOAD [Employee Name],

                                Location,

                                Site,

                                Dept,

                                [I/E]

                             

                             

                            FROM

                            [F:\CommunityHelp\Resource Planning v1.2.xlsx]

                            (ooxml, embedded labels, table is [Emp vs Role]);

                             

                            CrossTable(ROLE,FTE,2)

                            FACT:

                            LOAD [Employee Name],

                                'Individual'  as FACT_TYPE,

                                [Business Relationship (BRM)],

                                [Architect / PM],

                                Analyst,

                                Admin,

                                [Support & SME],

                                [Project Integrator],

                                Specialist,

                                Technician,

                                [Manager / Team Leader] ,

                                Enable

                            FROM

                            [F:\CommunityHelp\Resource Planning v1.2.xlsx]

                            (ooxml, embedded labels, table is [Emp vs Role]);

                             

                             

                            CrossTable(ROLE,FTE,2)

                            temp:

                            LOAD [Employee Name],

                            'TEAM' as FACT_TYPE,

                                [Alignment & Direction],

                                Performance,

                                [Sales, Marketing, and Logistics],

                                [Industrial, Quality, and Research],

                                [Finance, Purchasing, HR, & IT],

                                [Web Application Systems],

                                [Content Collaboration],

                                [Business Intelligence],

                                [B2B / EDI],

                                [Data Mart Management],

                                Testing,

                                [Innovation Center],

                                [Engineering and Architecture],

                                [Infrastructure and Operations],

                                OCM,

                                [Security and Risk],

                                [Client Services],

                                [Access Rights],

                                Symphony

                              

                            FROM

                            [F:\CommunityHelp\Resource Planning v1.2.xlsx]

                            (ooxml, embedded labels, table is [Emp vs Team]);

                             

                             

                             

                            Chart Expression for TEAM TOTAL

                            =Sum({<FACT_TYPE = {'TEAM'}>}FTE)

                             

                            Chart Expression for Employee TOTAL

                            =Sum({<FACT_TYPE = {'Indi*'}>}FTE)



                            crosstable.JPG

                              • Re: how to create a for loop?
                                Agrim Sharma

                                thanks a lot,where should i place your made script?

                                my script is like this:-

                                  • Re: how to create a for loop?
                                    Vineeth Pujari

                                    please use the script i posted As-IS, we are auto concatenating the data from Emp vs Role and Emp vs TEAM

                                     

                                    Changing the CrossTable field names will not work , and keep the employee information table separate like I have.

                                    Not sure why you are using left joins or mapping tables for

                                     

                                    LOAD [Employee Name],

                                        Location,

                                        Site,

                                        Dept,

                                        [I/E]

                                     

                                     

                                    FROM [lib://ResourcePlanning/Resource Planning v1.2.xlsx]

                                    (ooxml, embedded labels, table is [Emp vs Role]);

                                     

                                    Temp:

                                    CrossTable (ROLE,Value,2)

                                    LOAD [Employee Name],

                                      'Individual' as FACT_TYPE,

                                      [Business Relationship (BRM)],

                                      [Architect / PM],

                                      [Analyst],

                                      [Admin],

                                      [Support & SME],

                                      [Project Integrator],

                                      [Specialist],

                                      [Technician],

                                      [Manager / Team Leader],

                                      [Enable]

                                    FROM [lib://ResourcePlanning/Resource Planning v1.2.xlsx]

                                    (ooxml, embedded labels, table is [Emp vs Role]);

                                     

                                     

                                     

                                     

                                    CrossTable(ROLE,Value,2)

                                    LOAD [Employee Name],

                                         'Team' as FACT_TYPE,

                                         [Alignment & Direction],

                                         Performance,

                                         [Sales, Marketing, and Logistics],

                                         [Industrial, Quality, and Research],

                                         [Finance, Purchasing, HR, & IT],

                                         [Web Application Systems],

                                         [Content Collaboration],

                                         [Business Intelligence],

                                         [B2B / EDI],

                                         [Data Mart Management],

                                         Testing,

                                           [Innovation Center],

                                         [Engineering and Architecture],

                                         [Infrastructure and Operations],

                                         OCM,

                                         [Security and Risk],

                                         [Client Services],

                                         [Access Rights],

                                         Symphony

                                    FROM [lib://ResourcePlanning/Resource Planning v1.2.xlsx]

                                    (ooxml, embedded labels, table is [Emp vs Team]);

                                      • Re: how to create a for loop?
                                        Agrim Sharma

                                        Thanks for the quick reply.

                                         

                                        but my main concern is, i want to get conditional filter wise sum.

                                        that means,suppose when I select ROLE(filter)-Architect it should give me sum of "Values" of ROLE only=42.86(that's calculating correct in my dashboard),

                                        but along with it, when i select TEAM(filter)-finance=9.55(that's also calculating correct in my dashboard),

                                        but along with it,

                                        when i select filter of TEAM(Industrial). give me sum as 14.70(this value is calculating incorrectly) that should be 3.33.

                                         

                                         

                                        for your reference see this excel file,

                                        apply filter on ROLE-Architect

                                        apply filter on TEAM-finance

                                        and apply filter on TEAM-Industrial

                                        you will see1.PNG

                                                                  

                                         

                                        5 Names of Employee

                                        and if you check sum of TEAM(Industrial and finance) will be 3.33

                                        this "5" i want as No. of Employee & head count as 3.33 in Dashboard.

                              • Re: how to create a for loop?
                                Peter Cammaert

                                Please move your discussion to a Qlik Sense forum. Thanks.