How to convert excel formula to QLIK

[gp_123]

Hello Everyone,
I want to convert below excel formula in Qlik. 
PFA Excels,

Formula(Column AT) :
=IFERROR(IF(AND($H3="In progress",$AB3="",$L3<>""),"WF",IF(AND($H3="In progress",$AB3<>""),"E2E set up",IF(AND($H3="In progress",$L3=""),"Awating material creation",(VLOOKUP(CONCATENATE($A3,"Assigned",1),Tasks!$A:$H,8,0)))))," ")

Thanks In Advance:)

[gp_123]

@justISO  Do you have any idea on this?

[justISO · Accepted solution]

Hi again, as your excel formula is using vlookup, I would take this approuch: we are loading everything (as what is needed) from DATA excel, additionally we create field 'vlookup_key'  and searching does this key exist in 'Task lookup' table (LEFT JOIN). So now what is left is to convert your excel formula and add it as field '[Task pending 1 NEW]'. For this we load everything again and calculate that. Script could look like this:

DATA_temp:
LOAD
*,
[Number]&'Assigned'&1 as vlookup_key
FROM [lib://AttachedFiles/DATA.xlsx]
(ooxml, embedded labels, table is Sheet1);

LEFT JOIN
LOAD
[Concatenate] as vlookup_key,
[Assignment group]
FROM [lib://AttachedFiles/Task_Lookup.xlsx]
(ooxml, embedded labels, table is Sheet1);

DATA:
LOAD
*,
if( isnull(IF([State]='In progress' and len([Intercompany Processing start date])=0 and len([AGI code])<>0 , 'WF',
IF([State]='In progress' and len([Intercompany Processing start date])<>0, 'E2E set up',
IF([State]='In progress' and len([AGI code])=0, 'Awating material creation', [Assignment group]))) ), '') as [Task pending 1 NEW]
RESIDENT DATA_temp;

drop table DATA_temp;

[gp_123]

Thank You Once again:)