9 Replies Latest reply: Feb 18, 2016 4:29 AM by Sangram Reddy RSS

    How to convert this formula from excel to Qlik Sense Desktop

    Mike Starling

      Hi Everyone,

       

      I have been using Qlik Sense for just over 6 months. I love the program.

       

      I just started a new job and am rebuilding my Qlik Sense for the new role.  I am struggling with the formula below. It works great in Excel but I can't get this to work in Qlik Sense.  Can someone help to convert this to Qlik format?

       

      =IF(COUNTIF(AP2:BM2,"*Approved/Closed*")=2,"",INDEX(AP2:BM2,LOOKUP(2^999,SEARCH("Approved/Closed",AP2:BM2),COLUMN(AP2:BM2)-COLUMN(AP2))))

       

      Thank You,

       

      Mike

        • Re: How to convert this formula from excel to Qlik Sense Desktop
          Petter Skjolden

          If you explain what the formula is supposed to do it will be easier for many of us to suggest some solution in Qlik Sense.

          • Re: How to convert this formula from excel to Qlik Sense Desktop
            Sangram Reddy

            Hi Michael,

             

            I am sure that the formula can be replicated using a liitle of scripting and some set analysis statements. Is it possible to share a sample QVF?

             

            Thanks,

            Sangram.

              • Re: How to convert this formula from excel to Qlik Sense Desktop
                Mike Starling

                Thank you for the quick reply.

                 

                I have tried to attach the spreadsheet and cant see a way to do this. So I have added a picture of the columns.Qlik Example PIC.png

                 

                You will see multiple columns with "Action Date" and multiple columns with "Action Type [Performed By]".  When I upload these to Qlik sense it assigns numbers behind each column since they all have the same column header.  For example. "Action Date", "Action Date1", "Action Date2"...etc etc.

                 

                The point of my previous excel formula is to search across all of these columns and return the name of the person with the Action Type of "Approved/Closed".  This could be in the second Action Type column or the 10th action type column. 

                 

                And then I need a second formula to search for "Approved/Closed" and then return the time and date for that action which will be located in the "Action Date" column.

                  • Re: How to convert this formula from excel to Qlik Sense Desktop
                    bruno bertels

                    Hi

                     

                    To achieve your requierment in qlik sense i think you will need first to load your data with a crosstable statement in the meaning of concatenate all the same named column as dimensions.

                     

                    i understand that your excel file is like a tickets with enterring point and sorting point thrue a processus of escalation and send back

                    so one ticket can have one or three or ten escalations and for each you have a action time and action type.

                     

                    It will be easier to rearrange your data model from excel first in your load script.

                     

                    But a little bit too difficult for me in this case , sorry May be will be able to help you

                     

                    Bruno

                    • Re: How to convert this formula from excel to Qlik Sense Desktop
                      Petter Skjolden

                      This is probably the fastest way of restructuring your column oriented actions into only the approved ones and their corresponding dates:

                       

                      OnlyApproved:
                      LOAD
                        RowNo() AS Row#,
                        ActionDate
                      WHERE
                        ActionDate = 'Approved';
                      LOAD
                        Alt( Dual(Z,Y),Dual(X,W),Dual(V,U),Dual(T,S),Dual(R,Q),Dual(P,O),Dual(N,M),Dual(L,K),Dual(J,I),Dual(H,G),Dual(F,E),Dual(D,C),Dual(B,A) ) AS ActionDate
                      FROM
                        [Actions.xlsx]
                           (ooxml, header is 1 lines, no labels, table is Sheet1)
                      ;
                      

                       

                      The logic is built on an exact number of possible actions that is 13. Hence 26 columns and the A-Z names of them. A and B is Date and ActionType for the first event and there can be a maxium of 13 of them.

                       

                      The Alt() - function will pick out the last date that is entered. It is working in descending order of the alphabet z-a to pick the last entered valid date ( which is a number ). To keep the corresponding action together with the date we make use of the Dual()-function.

                       

                      Line 5 and 6 make sure that the last action actually is an 'Approved' action.

                       

                       

                      If you want to keep ALL actions that is in the original table you could have a load script like this:

                       

                      AllEvents:
                      LOAD
                        R#, Dual( Action, Date ) AS Action_Date
                      WHERE
                        Not( IsNull( Date ) );
                      LOAD
                        R#,
                        Date( Pick( IterNo() , A , C , E , G , I , K , M , O , Q , S , U , W , Y ) ) AS Date,
                             Pick( IterNo() , B , D , F , H , J , L , N , P , R , T , V , X , Z )   AS Action
                      WHILE
                        IterNo() <= 13
                      ;
                      LOAD 
                        RecNo()-1 AS R#, 
                        *
                      FROM
                        [Actions.xlsx]
                      (ooxml, no labels, table is Sheet1)
                      WHERE
                        RecNo()>1
                      ;
                      
                        • Re: How to convert this formula from excel to Qlik Sense Desktop
                          Mike Starling

                          Hi Petter,

                           

                          I think we are on the right track. We are just missing something.

                           

                          Here is the script I loaded.

                           

                          OnlyApproved:

                          LOAD

                            RowNo() AS Row#,

                            ActionDate

                          WHERE

                            ActionDate ='Approved/Closed';

                          LOAD

                            Alt( Dual(Z,Y),Dual(X,W),Dual(V,U),Dual(T,S),Dual(R,Q),Dual(P,O),Dual(N,M),Dual(L,K),Dual(J,I),Dual(H,G),Dual(F,E),Dual(D,C),Dual(B,A) ) AS ActionDate

                          FROM

                             

                               (ooxml, header is 1 lines, no labels, table is Workflow_Report)

                          ;

                           

                          Nothing is actually displaying on the new “Only Approved” script.  Only the column headers.

                           

                          I am wondering if it’s because ‘Approved/Closed’ is not a common field.  It changes with the name of each person that closes the WF.

                          Ie:

                          Approved/Closed (XXXXXX, Laura Maria[Experts Team. 2])

                           

                          Approved/Closed (XXXXX, Boglárka[Experts Team. 2])

                           

                          Approved/Closed (XCXXXX, Katia[Experts Team. 2])

                           

                           

                          Any ideas?

                           

                          Mike

                            • Re: How to convert this formula from excel to Qlik Sense Desktop
                              Mike Starling

                              I also tried to load the other script. (Which is actually the preferred Script)

                               

                              AllEvents: 

                              LOAD 

                                R#, Dual( Action, Date ) AS Action_Date 

                              WHERE 

                                Not( IsNull( Date ) ); 

                              LOAD 

                                R#, 

                                Date( Pick( IterNo() , A , C , E , G , I , K , M , O , Q , S , U , W , Y ) ) AS Date, 

                                     Pick( IterNo() , B , D , F , H , J , L , N , P , R , T , V , X , Z )   AS Action 

                              WHILE  

                                IterNo() <= 13 

                              LOAD  

                                RecNo()-1 AS R#,  

                                * 

                              FROM [lib://Qlik Daily Load/FEB 17th Data- Client Service Team.xlsx]

                              (ooxml, embedded labels, table is Workflow_Report)

                              WHERE 

                                RecNo()>1 

                              ;  

                               

                              And for some reason it gives the error Field not found - <A>

                               

                              Do you know why I would get this error?

                               

                              These columns of data actually change position in the excel file. One day they can start on column BA and the next they can start on column BB.  Is this a problem?

                                • Re: How to convert this formula from excel to Qlik Sense Desktop
                                  Petter Skjolden

                                  Yes - my example file was meant to only be a guideline - since you have requirements that you didn't state in the first place.

                                   

                                  1) Yes the script caters for a table that is placed in the columns A-Z - not anywhere else.

                                   

                                  2) The part of the load script which reads:

                                            WHERE

                                                 Action = 'Approved/Closed'

                                   

                                        Should be changed to:

                                            WHERE

                                                 Action Like 'Approved/Closed*'

                            • Re: How to convert this formula from excel to Qlik Sense Desktop
                              Sangram Reddy

                              Hi Michael,

                               

                              You will have to acheive this in the script itself. As you want to search through all the columns at the same time, you need to concatenated the values in these columns (which you would like to search) into the same column so that you will be able to perform a search on a field.

                               

                              As Petter has suggested, the Approved/Closed should work fine too.

                               

                              Thanks,

                              Sangram.