topic Re: logical condition in QlikView

logical condition

buzzy996 — Tue, 03 Nov 2015 13:21:51 GMT

hi ,

some one can help,to get the attached "expected output column" in excel?

Re: logical condition

sunny_talwar — Tue, 03 Nov 2015 13:28:40 GMT

Not really sure how you are getting the number 5 there. Can you explain the calculation again?

Re: logical condition

buzzy996 — Tue, 03 Nov 2015 13:39:41 GMT

hi sunny,

sorry i missed one record on my previous attachment file,pfa update one.

thank you

Re: logical condition

sunny_talwar — Tue, 03 Nov 2015 14:00:26 GMT

Is not the best of the solution, but with your current data which only goes back to the max of two rows this is working.

Table1:

LOAD *,

If(Len(Trim(CalcDate)) > 0, Interval(LASTCHANGEDTS - CalcDate, 'd')) as OutPut;

LOAD TASK_REF,

TASK_VERSION_KEY,

VERSIONID,

STATUS,

CREATETS,

LASTCHANGEDTS,

If(STATUS = 'Reply Received',

If(Previous(Previous(STATUS)) = 'Awaiting Reply' and TASK_REF = Previous(Previous(TASK_REF)), Previous(Previous(LASTCHANGEDTS)),

If(Previous(STATUS) = 'Awaiting Reply' and TASK_REF = Previous(TASK_REF), Previous(LASTCHANGEDTS)))) as CalcDate

FROM

[In and output.xls]

(biff, embedded labels, table is Sheet1$);

Depending on how much back you expect your data to go you can expand the following If statement:

If(STATUS = 'Reply Received',

If(Previous(Previous(STATUS)) = 'Awaiting Reply' and TASK_REF = Previous(Previous(TASK_REF)), Previous(Previous(LASTCHANGEDTS)),

If(Previous(STATUS) = 'Awaiting Reply' and TASK_REF = Previous(TASK_REF), Previous(LASTCHANGEDTS)))) as CalcDate

Output:

Re: logical condition

buzzy996 — Tue, 03 Nov 2015 14:25:56 GMT

Hi Sunny,

yes,your assumption is right..data will change dynamically.

instituted of writing if with previous(previosu()),there is any other solution?

thanks for ur help.

Re: logical condition

sunny_talwar — Tue, 03 Nov 2015 14:31:54 GMT

There must be, just can't think of anything right now. May be swuehl‌ or MarcoWedel‌ can help here

Re: logical condition

swuehl — Tue, 03 Nov 2015 14:49:44 GMT

I think you can do it something along these lines:

INPUT:

LOAD TASK_REF,

TASK_VERSION_KEY,

VERSIONID,

STATUS,

CREATETS,

LASTCHANGEDTS

FROM

[In and output (1).xls]

(biff, embedded labels, table is Sheet1$);

LOAD *,

if(STATUS = 'Reply Received', LASTCHANGEDTS- AwaitDTS) as Output;

LOAD TASK_REF,

TASK_VERSION_KEY,

VERSIONID,

STATUS,

CREATETS,

LASTCHANGEDTS,

if(STATUS = 'Awaiting Reply' and not (peek('STATUS') ='Awaiting Reply' and TASK_REF = peek('TASK_REF')), LASTCHANGEDTS, PEEK('AwaitDTS')) AS AwaitDTS

Resident INPUT

ORDER BY TASK_REF, LASTCHANGEDTS;

DROP TABLE INPUT;

Re: logical condition

buzzy996 — Tue, 03 Nov 2015 14:56:01 GMT

hi swuehl

i'll check and update to u,thank you so much.

thnanks

Re: logical condition

Kushal_Chawda — Wed, 04 Nov 2015 06:17:34 GMT

Data:

LOAD TASK_REF,

TASK_VERSION_KEY,

VERSIONID,

STATUS,

CREATETS,

LASTCHANGEDTS

FROM

[In and output (1).xls]

(biff, embedded labels, table is Sheet1$);

New:

LOAD *,

if(STATUS='Reply Received',LASTCHANGEDTS-ChangeDate,Null()) as OutPut;

LOAD *,

if(TASK_REF<>Previous(TASK_REF) and STATUS='Awaiting Reply', LASTCHANGEDTS,

if(TASK_REF<>Previous(TASK_REF) and STATUS='Reply Received',Null(),

if(TASK_REF=Previous(TASK_REF) and (previous(STATUS)='Reply Received' and STATUS= 'Awaiting Reply'),LASTCHANGEDTS,

if(TASK_REF=Previous(TASK_REF) and previous(STATUS)='Awaiting Reply',Peek('ChangeDate'),null())))) as ChangeDate

Resident Data

Order by TASK_REF,VERSIONID asc;

DROP Table Data;