topic Re: How to convert excel formula to QLIK in App Development

How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 07:13:12 GMT

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

Re: How to convert excel formula to QLIK

justISO — Wed, 24 Aug 2022 07:49:56 GMT

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, '')))

Re: How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 08:16:45 GMT

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

Re: How to convert excel formula to QLIK

justISO — Wed, 24 Aug 2022 08:21:19 GMT

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.

Re: How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 08:23:50 GMT

On script level is it possible?

Re: How to convert excel formula to QLIK

justISO — Wed, 24 Aug 2022 08:31:18 GMT

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:

Re: How to convert excel formula to QLIK

gp_123 — Wed, 24 Aug 2022 08:36:15 GMT

Thank You so much. It's working.🤗