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

# 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

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

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

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

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.

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

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.

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

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

```OnlyApproved:
RowNo() AS Row#,
ActionDate
WHERE
ActionDate = 'Approved';
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:
R#, Dual( Action, Date ) AS Action_Date
WHERE
Not( IsNull( Date ) );
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
;
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

Hi Petter,

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

Here is the script I loaded.

OnlyApproved:

RowNo() AS Row#,

ActionDate

WHERE

ActionDate ='Approved/Closed';

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

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

AllEvents:

R#, Dual( Action, Date ) AS Action_Date

WHERE

Not( IsNull( Date ) );

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

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

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.

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

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.