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

[jyothish8807]

Try This:

Load*,

if(peek(SNO,1)<=SNO and SNO<>24,'NONE',if([Employee ID]=Previous([Employee ID]) and DOMAIN<>previous(DOMAIN),DOMAIN,'')) as [Shifted To],

if(if(peek(SNO,1)<=SNO and SNO<>24,'NONE',if([Employee ID]=Previous([Employee ID]) and DOMAIN<>previous(DOMAIN),DOMAIN,''))<>'',DOMAIN,'') as [Shifted From];

LOAD * INLINE [

    Employee ID  , SNO, DOMAIN

    15689745, 13, QLIK

    15689745, 14, QLIK

    15689745, 15, QLIK

    15689745, 16, QLIK

    15689745, 17, QLIK

    15689745, 18, QLIK

    15689745, 19, QLIK

    15689745, 20, QLIK

    15689745, 21, QLIK

    15689745, 22, QLIK

    15689745, 23, QLIK

    15689745, 24, SAP

];

[big_dreams]

Need 1 correction only

try below

Load *,

           if([Shifted To]<> 'NONE',peek(DOMAIN,1),'NONE') as [Shifted From];

LOAD *,

if(peek(SNO,1)<=SNO and SNO<>24,'NONE',if([Employee ID]=Previous([Employee ID]) and DOMAIN<>previous(DOMAIN),DOMAIN,'')) as [Shifted To],

INLINE [

    Employee ID  , SNO, DOMAIN

    15689745, 13, QLIK

    15689745, 14, QLIK

    15689745, 15, QLIK

    15689745, 16, QLIK

    15689745, 17, QLIK

    15689745, 18, QLIK

    15689745, 19, QLIK

    15689745, 20, QLIK

    15689745, 21, QLIK

    15689745, 22, QLIK

    15689745, 23, QLIK

    15689745, 24, SAP

];

Regards,

[jyothish8807 · Accepted solution]

Hi,

Made some slight changes:

A:

LOAD * INLINE [

    Employee ID  , SNO, DOMAIN

    15689745, 13, QLIK

    15689745, 14, QLIK

    15689745, 15, QLIK

    15689745, 16, QLIK

    15689745, 17, QLIK

    15689745, 18, QLIK

    15689745, 19, QLIK

    15689745, 20, QLIK

    15689745, 21, QLIK

    15689745, 22, QLIK

    15689745, 23, QLIK

    15689745, 24, SAP

];

B:

Load*,

'' as Dummy,

peek('SNO',RowNo(),'A') as 1,

if(peek('SNO',RowNo(),'A')<=SNO and SNO<>24,'NONE',if([Employee ID]=Previous([Employee ID]) and DOMAIN<>previous(DOMAIN),DOMAIN,'')) as [Shifted To],

if(if(peek('SNO',RowNo(),'A')<=SNO and SNO<>24,'NONE',if([Employee ID]=Previous([Employee ID]) and DOMAIN<>previous(DOMAIN),DOMAIN,''))<>'',previous(DOMAIN),'') as [Shifted From]

resident A;

drop table A;

[mahitham]

Thanks Jyothish. Its working

[jyothish8807]

You are welcome