28 Replies Latest reply: Feb 12, 2017 11:33 PM by Agrim Sharma RSS

    Qlik Formula

    Agrim Sharma

      can anybody tell me how to write this excel formula in qlik sense script?

       

      =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[REF BUILD]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)

        • Re: Qlik Formula
          Sachin duganavar

          HI,

           

          You can go through this,there functions like look up and apply map in qlikview

           

          Joins and Lookups

           

           

          sachin

            • Re: Qlik Formula
              Sachin duganavar

              Or give excel data and mark there what all u want to look up

               

              sachin

                • Re: Qlik Formula
                  Agrim Sharma

                  ok i am attaching the Excel file for your reference.

                   

                  here in this file i have calculated Month wise (M1..M12)

                  1.REF BUILD

                  =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[REF BUILD]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)

                  2.OTHER

                  =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[OTHER]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)

                  3.RUN

                  =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[RUN]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)

                  4.Enable

                  =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[Enable]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)

                   

                   

                  I want to calculate these Four in qlik sense direclty not in excel file. can you please help?

                • Re: Qlik Formula
                  Agrim Sharma

                  Hi sachin can you convert this into qlik formula?

                • Re: Qlik Formula
                  Sasi Vardhan KV

                  By seeing the script:

                   

                  most of the things were vlookup, you can use Applymap().

                   

                  After the above mentioned formula what is the data getting in to the excel?

                  • Re: Qlik Formula
                    Sunny Talwar

                    I couldn't get the exact result, but you will get an idea here

                      • Re: Qlik Formula
                        Agrim Sharma

                        hi sunny can you send it by pasting in here as i do not have qlik view, i have qlik sense

                          • Re: Qlik Formula
                            Sunny Talwar

                            Here are bunch of mapping tables:

                             

                            MappingTable1:

                            Mapping

                            LOAD ROLE,

                                 [REF BUILD] & '|' & OTHER & '|' & RUN & '|' & Enable

                            FROM

                            [..\..\Downloads\Resource Planning.xlsx]

                            (ooxml, embedded labels, table is ROLES);

                             

                            Temp:

                            CrossTable (ROLE, Value)

                            LOAD [Employee Name],

                                 [Business Relationship (BRM)],

                                 [Architect / PM],

                                 Analyst,

                                 Admin,

                                 [Support & SME],

                                 [Project Integrator],

                                 Specialist,

                                 Technician,

                                 [Manager / Team Leader],

                                 Enable

                            FROM

                            [..\..\Downloads\Resource Planning.xlsx]

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

                             

                            MappingTable2:

                            Mapping

                            LOAD [Employee Name]&ROLE,

                              Value

                            Resident Temp;

                             

                            DROP Table Temp;

                             

                            Temp2:

                            CrossTable (Team, Value2)

                            LOAD [Employee Name],

                                 [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

                            [..\..\Downloads\Resource Planning.xlsx]

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

                             

                            MappingTable3:

                            Mapping

                            LOAD [Employee Name]&Team,

                              Value2

                            Resident Temp2;

                             

                            DROP Table Temp2;

                             

                            Temp3:

                            CrossTable(Month, Value3)

                            LOAD [EMP. NAME],

                                 Jan,

                                 Feb,

                                 Mar,

                                 Apr,

                                 May,

                                 Jun,

                                 Jul,

                                 Aug,

                                 Sep,

                                 Oct,

                                 Nov,

                                 Dec

                            FROM

                            [..\..\Downloads\Resource Planning.xlsx]

                            (ooxml, embedded labels, table is [Actual Working Days]);

                             

                            MappingTable4:

                            Mapping

                            LOAD [EMP. NAME]&Month(Date#(Month, 'MMM')),

                              Value3

                            Resident Temp3;

                             

                            DROP Table Temp3;

                             

                            and then one of the M tabs (M1)

                             

                            M1:

                            LOAD RowNo() as RowNo,

                              Month,

                                 [EMP. NAME],

                                 ROLE,

                                 Team,

                                 [TOTAL WORKING DAYS],

                                 SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],

                                 SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,

                                 SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,

                                 SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable

                            FROM

                            [..\..\Downloads\Resource Planning.xlsx]

                            (ooxml, embedded labels, table is M1);

                             

                            Like I mentioned, the results are not matching completely, but I think there might be a small mistake here or there

                             

                            Capture.PNG

                          • Re: Qlik Formula
                            Agrim Sharma

                            i simply want this formula to use directly in qlik sense.

                             

                            =(VLOOKUP([@ROLE],Table2[#All],MATCH(Table1[[#Headers],[Enable]],Table2[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table3[#All],MATCH([@ROLE],Table3[#Headers],0),FALSE)*VLOOKUP([@[EMP. NAME]],Table4[#All],MATCH([@Team],Table4[#Headers],0),FALSE))*VLOOKUP([@[EMP. NAME]],Table5[#All],2,)


                            can you please convert this formula into qlik language?

                              • Re: Qlik Formula
                                Sunny Talwar

                                Here it is

                                 

                                SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],

                                     SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,

                                     SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,

                                     SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable

                                  • Re: Qlik Formula
                                    Agrim Sharma

                                    hi sunny,

                                    i cant find some of the fields in this script like "location" can you tell me how to get it again

                                      • Re: Qlik Formula
                                        Sunny Talwar

                                        location? what tab was this on (in the Excel)

                                          • Re: Qlik Formula
                                            Agrim Sharma

                                            yes on excel

                                              • Re: Qlik Formula
                                                Sunny Talwar

                                                what tab?

                                                  • Re: Qlik Formula
                                                    Agrim Sharma

                                                    i was having a field name "Location" in excel but when we drop table

                                                    FROM

                                                    [..\..\Downloads\Resource Planning.xlsx]

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


                                                    i cant find it in qlik sense as field

                                                    • Re: Qlik Formula
                                                      Agrim Sharma

                                                      if you see my sent excel file you will find that in Emp vs Role sheet there is a field name 'Location' but i cant see it now in qlik sense

                                                        • Re: Qlik Formula
                                                          Sunny Talwar

                                                          Yes, I did not bring that in since it was not needed for the vlookup. You can bring that in as you did initially. I don't know how you did it previously

                                                            • Re: Qlik Formula
                                                              Agrim Sharma

                                                              Hi sunny,

                                                              hope you fine:)

                                                              thanks a lot for your kind help, i just need one more help in this script.

                                                              i cant see fields(Tabs) in qlik sense fields. i want all these fields to show in qlik.

                                                               

                                                              Fields.PNG

                                                               

                                                              [Location],

                                                                [Site],

                                                                [Dept],

                                                              [I/E],

                                                                [Business Relationship (BRM)],

                                                                [Architect / PM],

                                                                [Analyst],

                                                                [Admin],

                                                                [Support & SME],

                                                                [Project Integrator],

                                                                [Specialist],

                                                                [Technician],

                                                                [Manager / Team Leader],

                                                                [Enable]

                                                               

                                                               

                                                              **below is your provided script**

                                                               

                                                              MappingTable1:

                                                              Mapping

                                                              LOAD ROLE,

                                                                   [REF BUILD] & '|' & OTHER & '|' & RUN & '|' & Enable

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

                                                              (ooxml, embedded labels, table is ROLES);

                                                               

                                                              Temp:

                                                              CrossTable (ROLE, Value)

                                                              LOAD [Employee Name],

                                                                [Location],

                                                                [Site],

                                                                [Dept],

                                                                [I/E],

                                                                [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]);

                                                               

                                                              MappingTable2:

                                                              Mapping

                                                              LOAD [Employee Name]&ROLE,

                                                                Value

                                                               

                                                              Resident Temp;

                                                              DROP Table Temp;

                                                               

                                                              Temp2:

                                                              CrossTable (Team, Value2)

                                                              LOAD [Employee Name],

                                                                   [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]);

                                                               

                                                              MappingTable3:

                                                              Mapping

                                                              LOAD [Employee Name]&Team,

                                                                Value2

                                                              Resident Temp2;

                                                               

                                                              DROP Table Temp2;

                                                               

                                                              Temp3:

                                                              CrossTable(Month, Value3)

                                                              LOAD [EMP. NAME],

                                                                   Jan,

                                                                   Feb,

                                                                   Mar,

                                                                   Apr,

                                                                   May,

                                                                   Jun,

                                                                   Jul,

                                                                   Aug,

                                                                   Sep,

                                                                   Oct,

                                                                   Nov,

                                                                   Dec

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

                                                              (ooxml, embedded labels, table is [Actual Working Days]);

                                                               

                                                              MappingTable4:

                                                              Mapping

                                                              LOAD [EMP. NAME]&Month(Date#(Month, 'MMM')),

                                                                Value3

                                                              Resident Temp3;

                                                               

                                                              DROP Table Temp3;

                                                               

                                                               

                                                              M1:

                                                              LOAD RowNo() as RowNo,

                                                                Month,

                                                                   [EMP. NAME],

                                                                   ROLE,

                                                                   Team,

                                                                   [TOTAL WORKING DAYS],

                                                                   SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],

                                                                   SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,

                                                                   SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,

                                                                   SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable

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

                                                              (ooxml, embedded labels, table is M1);

                                                                • Re: Qlik Formula
                                                                  Sunny Talwar

                                                                  Can you share the script you were using before this script? or did you not have a script before?

                                                                    • Re: Qlik Formula
                                                                      Agrim Sharma

                                                                      attached is my old script,

                                                                      i wanted to calculate month wise(M1...M12)

                                                                      Enable, Other, Run, Ref. Build

                                                                       

                                                                      with the help of your provided formula(For each Month Jan-Dec) so that i do not have to use M1...M12 in making Dashboard,

                                                                      with the help of this formula i wanted to calculate month wise Enable, Other, Run, Ref. Build without including M1...M12 in qlik sense tables.

                                                                       

                                                                       

                                                                      SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 1) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as [REF BUILD],

                                                                       

                                                                       

                                                                           SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 2) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as OTHER,

                                                                       

                                                                       

                                                                           SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 3) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as RUN,

                                                                       

                                                                       

                                                                           SubField(ApplyMap('MappingTable1', ROLE, 0), '|', 4) * ApplyMap('MappingTable2', [EMP. NAME]&ROLE, 0) * ApplyMap('MappingTable3', [EMP. NAME]&Team, 0) * ApplyMap('MappingTable4', [EMP. NAME]&Month(Date#(Month, 'MMMM')), 0) as Enable

                                                                       

                                                                      Thanks a lot for your replies it really helps me a lot

                                                                      • Re: Qlik Formula
                                                                        Agrim Sharma

                                                                        hi sunny,

                                                                        hope you fine

                                                                        i attached my old script if you get some time please see that

                                                                        thanks