Thank you for the quick reply.
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.
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 Sangram Reddy will be able to help you
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 ;
I think we are on the right track. We are just missing something.
Here is the script I loaded.
RowNo() AS Row#,
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
(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.
Approved/Closed (XXXXXX, Laura Maria[Experts Team. 2])
Approved/Closed (XXXXX, Boglárka[Experts Team. 2])
Approved/Closed (XCXXXX, Katia[Experts Team. 2])
I also tried to load the other script. (Which is actually the preferred Script)
R#, Dual( Action, Date ) AS Action_Date
Not( IsNull( Date ) );
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
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)
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?
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:
Action = 'Approved/Closed'
Should be changed to:
Action Like 'Approved/Closed*'
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.