topic Re: Please help in QlikView

Please help

Tue, 09 Oct 2012 09:30:19 GMT

If we have to fetch second value from no of values. How to get it.

Fr example..

I have a file with date 24-sep-2012 timestamps. here there is some flag. The flag becoming suspended to inprogress.In this case how can i get the difference

b/w second suspened and second inprogress.

flag datefield

dispatched 24-sep-2012 2:30:23

delivered 24-sep-2012 3:30:23

inprogress 24-sep-2012 3:50:23

completed 24-sep-2012 4:30:23

suspended 24-sep-2012 5:30:23

inprogress 24-sep-2012 6:30:23

suspended 24-sep-2012 7:30:23

Re: Please help

swuehl — Tue, 09 Oct 2012 09:37:28 GMT

try something like

=interval( min({<flag = {'suspended'}>} datefield, 2) - min({<flag = {'inprogress'}>} datefield, 2) )

So min(FIELD, 2) should get you the second lowest value of FIELD

edit:

I assumed that your standard timestamp format set at the top of your script matches your datefield format. If not, you should adapt the standard timestamp format or use timestamp#(datefield, 'DD-MMM-YYYY hh:mm:ss') to interprete your field values correctly as timestamp.

Re: Please help

Tue, 09 Oct 2012 09:43:20 GMT

thanks. I will try and ask if needed some more help.

Re: Please help

Tue, 09 Oct 2012 09:43:28 GMT

thanks. I will try and ask if needed some more help.

Re: Please help

Tue, 09 Oct 2012 10:06:04 GMT

Hi,

Swuehl's answer is correct.

Maybe you could try this for a more specific format.

=interval(Date(min({<flag = {'suspended'}>} Date#(datefield,'DD-MMM-YYYY hh:mm:ss'),2),'YYYY-MM-DD hh:mm:ss')-

Date(min({<flag = {'inprogress'}>} Date#(datefield,'DD-MMM-YYYY hh:mm:ss'),2),'YYYY-MM-DD hh:mm:ss'),'hh:mm:ss')

Regards,

Janzen

Re: Please help

Tue, 09 Oct 2012 11:48:01 GMT

I have a similar query.

Here there is some flag and datefield. The flag becoming no to yes and vice versa.In this case how can I get the difference b/w minimum second "no" and minimum first "yes". i.e 24-sep-2012 6:30:23 - 24-sep-2012 3:50:23

flag datefield

No 24-sep-2012 2:30:23

No 24-sep-2012 3:30:23

Yes 24-sep-2012 3:50:23

Yes 24-sep-2012 4:30:23

Yes 24-sep-2012 5:30:23

No 24-sep-2012 6:30:23

No 24-sep-2012 7:30:23

Re: Please help

Tue, 09 Oct 2012 13:03:57 GMT

In the post before added, from that I just want to know that how to get the second no after yes.

This needs to be done at script level. Suggest me.

Re: Please help

Tue, 09 Oct 2012 13:06:58 GMT

Here there is some flag and datefield. The flag becoming no to yes and vice versa.In this case how can I capture the date 24-sep-2012 6:30:23 i.e second no after yes.

flag datefield

No 24-sep-2012 2:30:23

No 24-sep-2012 3:30:23

Yes 24-sep-2012 3:50:23

Yes 24-sep-2012 4:30:23

Yes 24-sep-2012 5:30:23

No 24-sep-2012 6:30:23 ----> How can i capture it in script. What is the query needs to be written. Please help.

No 24-sep-2012 7:30:23

Re: Please help

Tue, 09 Oct 2012 13:28:12 GMT

JeopardyAuditTemp:Load date(min(AuditTimeStamp),'DD/MM/YYYY h:mm:ss[.fff] TT') as MinAuditTimeStamp0 ,
     W6Key,
     Audit_AssignedEngineer
      FROM
[$(vAppQvdPath)\Audit$(vTableSuffix).qvd]
(qvd)where Audit_InJeopardy='No' Group by W6Key,Audit_AssignedEngineer,Audit_InJeopardy;

Currently I am capturing minimum AuditTimeStamp of first "No" i.e 24-sep-2012 2:30:23.

But I need to capture mininmum AuditTimeStamp of Second "no". i.e 24-sep-2012 6:30:23

How do get second no please suggest. This i need to get at script level itself.

Audit_InJeopardy AuditTimeStamp

No 24-sep-2012 2:30:23

No 24-sep-2012 3:30:23

Yes 24-sep-2012 3:50:23

Yes 24-sep-2012 4:30:23

Yes 24-sep-2012 5:30:23

No 24-sep-2012 6:30:23 ----> How can i capture it in script.

No 24-sep-2012 7:30:23

Re: Please help

CELAMBARASAN — Tue, 09 Oct 2012 15:07:10 GMT

try with this

JeopardyAuditTemp:

Load * Where counter=2;

Load date(min(AuditTimeStamp),'DD/MM/YYYY h:mm:ss[.fff] TT') as MinAuditTimeStamp0 ,
W6Key,

if((previous(W6Key & Audit_AssignedEngineer & Audit_InJeopardy)<>(

W6Key & Audit_AssignedEngineer & Audit_InJeopardy),1,peek('counter')+1) AS counter,

Audit_AssignedEngineer
FROM
[$(vAppQvdPath)\Audit$(vTableSuffix).qvd]
(qvd)where Audit_InJeopardy='No' Group by W6Key,Audit_AssignedEngineer,Audit_InJeopardy;

Re: Please help

swuehl — Tue, 09 Oct 2012 15:46:31 GMT

You can try it like this, creating a group for each block of yes / no and filtering the no flags starting from the third block:

Set TimestampFormat = 'DD-MMM-YYYY h:mm:ss';

INPUT:

LOAD *,

if(peek(Audit_InJeopardy)=Audit_InJeopardy,peek(Group),rangesum(peek(Group),1)) as Group

INLINE [

Audit_InJeopardy, AuditTimeStamp

No, 24-sep-2012 2:30:23

No, 24-sep-2012 3:30:23

Yes, 24-sep-2012 3:50:23

Yes, 24-sep-2012 4:30:23

Yes, 24-sep-2012 5:30:23

No, 24-sep-2012 6:30:23

No, 24-sep-2012 7:30:23

];

JeopardyAuditTemp:

Load date(min(AuditTimeStamp),'DD/MM/YYYY h:mm:ss[.fff] TT') as MinAuditTimeStamp0

// W6Key,

// Audit_AssignedEngineer

resident INPUT

where Audit_InJeopardy='No' and Group > 2 // no matter if Audit_InJeopardy starts with yes or no

Group by /*W6Key,Audit_AssignedEngineer,*/

Audit_InJeopardy;

I just commented W6Key and Audit_AssignedEngineer out (no data for those fields and no idea what they mean).

Hope this helps,

Stefan