Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to convert this formula from excel to Qlik Sense Desktop

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

9 Replies
petter
MVP
MVP

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
Master II
Master II

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.

Not applicable
Author

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.

brunobertels
Specialist II
Specialist II

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

petter
MVP
MVP

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

;

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

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
Master II
Master II

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.