if logic calculation help

[mahitham]

Hi Experts,

Can any one please help me to calculate the shifted From and Shifted To columns by Employee ID,SNO and DOMAIN columns in Qlik.

The Excel calculations For

Shifted To is =IF(AND(B3<=B2,B2<>24),"NONE",IF(AND(A2=A1,C2<>C1),C2,""))

Shifted From is =IF(E2<>"",C1,"")

Thanks in advance

[adityaakshaya]

Hi Mahitha,

I am assuming that you need this logic to be implemented in the script. Using peek within if can help you to achieve this in the script. Please find attached application for your reference.

Make sure to order your data before using Peek

Also, below are the expression I used.

Shifted To :

If(SNO<=Peek(SNO,1,'Test') and SNO<>24,'NONE',if([Employee ID]= Peek([Employee ID],-1) and DOMAIN<>Peek(DOMAIN,-1),DOMAIN,'')) as [Shifted To]

Shifted From :

if(Len(If(SNO<=Peek(SNO,1,'Test') and SNO<>24,'NONE',if([Employee ID]= Peek([Employee ID],-1) and DOMAIN<>Peek(DOMAIN,-1),DOMAIN,'')))<>0,Peek(DOMAIN,-1),'') as [Shifted From]

Regards,

Akshaya

PS - If you find this response as Correct/Helpful, mark it as Correct.