topic Re: if logic calculation help in QlikView

if logic calculation help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: if logic calculation help

jyothish8807 — Mon, 21 May 2018 08:41:58 GMT

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

];

Re: if logic calculation help

big_dreams — Mon, 21 May 2018 08:53:57 GMT

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,

Re: if logic calculation help

jyothish8807 — Mon, 21 May 2018 10:03:10 GMT

Hi,

Made some slight changes:

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

];

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;

Re: if logic calculation help

mahitham — Mon, 21 May 2018 12:34:35 GMT

Thanks Jyothish. Its working

Re: if logic calculation help

jyothish8807 — Mon, 21 May 2018 12:45:30 GMT

You are welcome