topic How to convert excel formula to QLIK in App Development

How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 10:02:16 GMT

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:)

Re: How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 10:17:37 GMT

@justISO Do you have any idea on this?

Re: How to convert excel formula to QLIK

justISO — Wed, 24 Aug 2022 10:50:19 GMT

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;

Re: How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 11:22:09 GMT

Thank You Once again:)