How to convert this formula from excel to Qlik Sense Desktop

[#1]

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

[petter]

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.

[reddy-s]

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.

[#4]

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.

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.

[brunobertels]

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 Reddys310 will be able to help you

Bruno

[petter]

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
;

[#7]

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

[#8]

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?

[petter]

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*'

[reddy-s]

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.