How to convert excel formula to QLIK

[gp_123]

Hello Everyone,
Good Day!!!

PFA Excel,
I want to convert below excel formula in qlik.

Formula( Column B in Excel )  : 
IF(AND(C2<>C1,F2="Assigned"),1,IF(AND(C2=C1,F2<>F1,F2="Assigned"),1,IF(AND(C2=C1,F2=F1,F2="Assigned"),2,"")))

Thanks in advance:)

[justISO]

Hi, it looks something like this, if it will be used in report level:

=if([Parent]<>above([Parent]) and [State]='Assigned', 1,
if([Parent]=above([Parent]) and [State]<>above([State]) and [State]='Assigned', 1,
if([Parent]=above([Parent]) and [State]=above([State]) and [State]='Assigned', 2, '')))

[gp_123]

Hi @justISO 
Thanks for the reply.
Is it possible to you to share sample app because the above formula is not working on script level and on frontend it is not showing any error but "Invalid Dimension" is coming. 
Once again thanks:)

[justISO]

You want to calculate this in 'script level' or 'frontend'? In this formula fields [Parent] and [State] should represent your equivalent fields (as in excel) and if they named differently, formula should be adjusted accordingly.

[gp_123]

On script level is it possible?

[justISO · Accepted solution]

Yes. So I took some data from your excel as sample and loaded simple table with columns 'Parent', 'Number', 'Created' and 'State' (table 'temp'). Then I load everything again into table 'main', but now I make this 'Check' column according your formula. Notice, that in script level above() function is changed to previous():

temp:
load * inline [
Parent,	Number,	Created,	State,
CPM0001009,	CPT0001001,	2019-10-14 6:45,	Closed complete,
CPM0001138,	CPT0001002,	2019-10-14 11:47,	Closed complete,
CPM0001138,	CPT0001003,	2019-10-14 11:48,	Closed complete,
CPM0001138,	CPT0001004,	2019-10-14 11:50,	Assigned,
CPM0001138,	CPT0001005,	2019-10-14 11:51,	Closed complete,
CPM0001140,	CPT0001006,	2019-10-14 12:06,	Closed complete,
CPM0001140,	CPT0001007,	2019-10-14 12:07,	Closed complete,
];


NoConcatenate
main:
load 
*,
if([Parent]<>previous([Parent]) and [State]='Assigned', 1,
if([Parent]=previous([Parent]) and [State]<>previous([State]) and [State]='Assigned', 1,
if([Parent]=previous([Parent]) and [State]=previous([State]) and [State]='Assigned', 2, ''))) as Check
resident temp;

drop table temp;

 Result:

[gp_123]

Thank You so much. It's working.🤗