topic Re: How to find date wise final status in QlikView

How to find date wise final status

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

hi all

I have data like the following table, I want to show Date wise final status for JobNo

Result should be like

Re: How to find date wise final status

sunny_talwar — Wed, 28 Jun 2017 23:20:05 GMT

Script or front end?

Re: How to find date wise final status

antoniotiman — Wed, 28 Jun 2017 23:30:21 GMT

Hi,

see Attachment.

Regards,

Antonio.

Re: How to find date wise final status

vengadeshpalani — Thu, 29 Jun 2017 14:21:49 GMT

in script level Sunny

Re: How to find date wise final status

sunny_talwar — Thu, 29 Jun 2017 14:30:05 GMT

May be this

Table:

LOAD JobNo,

AutoNumber(JobNo&Floor(JobStatusDateTime)) as Key,

Date(Floor(JobStatusDateTime)) as JobStatusDate,

JobStatusDateTime,

JobStatusDescription,

Match(JobStatusDescription, 'RECEIVED', 'ASSIGNED')+1 as Flag

FROM

[test1.xls]

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

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Key <> Previous(Key)

Order By Key, JobStatusDateTime desc, Flag desc;

DROP Table Table;

The only thing that I was confused about was, if two things had the same exact timestamp... which one would you choose?

Re: How to find date wise final status

antoniotiman — Thu, 29 Jun 2017 14:51:58 GMT

In script Level

Temp:
LOAD JobNo,
JobStatusDateTime+AutoNumber(RowNo(),
JobStatusDateTime)/100000000 as JobStatusDateTime,
Date(Floor(JobStatusDateTime)) as JobStatusDate,
JobStatusDescription
FROM
https://community.qlik.com/servlet/JiveServlet/download/1297227-285159/test1.xls
(biff, embedded labels, table is Input$);
NoConcatenate
LOAD JobNo,(JobStatusDate) as JobStatusDate,
FirstSortedValue(DISTINCT JobStatusDescription,-JobStatusDateTime) as JobStatusDescription
Resident Temp
Group By JobNo,JobStatusDate;
Drop Table Temp;

Re: How to find date wise final status

effinty2112 — Thu, 29 Jun 2017 15:06:37 GMT

Hi Vengadesh,

Here s another :

SourceTable:

LOAD

JobNo,

Date(floor(JobStatusDateTime)) as JobStatusDate,

JobStatusDateTime,

JobStatusDescription

FROM

test1.xls

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

Table:

Load

JobNo,

JobStatusDate,

FirstSortedValue(JobStatusDescription,-JobStatusDateTime) as JobStatus

Resident SourceTable

Group by JobNo, JobStatusDate;

drop table SourceTable;

Gives this table with a couple of blanks because of the simultaneous JobStatusDateTime value for two Jobstatuses

JobNo	JobStatusDate	JobStatus
GAS-20170115-0232	15/01/2017
GAS-20170115-0232	19/01/2017	ASSIGNED
GAS-20170115-0232	11/02/2017	ACCEPTED
GAS-20170115-0232	28/06/2017	ASSIGNED
GAS-20170115-1400	15/01/2017
GAS-20170115-1400	17/01/2017	ASSIGNED
GAS-20170115-1400	25/05/2017	ASSIGNED
GAS-20170115-1400	07/06/2017	ASSIGNED
GAS-20170115-1400	26/06/2017	CLEARED
GAS-20170115-1400	27/06/2017	DISPOSE

cheers

Andrew

Re: How to find date wise final status

effinty2112 — Thu, 29 Jun 2017 15:16:13 GMT

Hi Vengadesh,

This is a bit of fun:

SourceTable:

LOAD

JobNo,

Date(floor(JobStatusDateTime)) as JobStatusDate,

JobStatusDateTime,

JobStatusDescription

FROM

test1.xls

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

NoConcatenate

AggrTable:

LOAD

JobNo,

JobStatusDate,

JobStatusDateTime,

Concat(JobStatusDescription,', ') as JobStatusDescription

Resident SourceTable

Group by JobNo, JobStatusDate,JobStatusDateTime;

drop table SourceTable;

Table:

Load

JobNo,

JobStatusDate,

FirstSortedValue(JobStatusDescription,-JobStatusDateTime) as JobStatus

Resident AggrTable

Group by JobNo, JobStatusDate;

drop table AggrTable;

will give you this showing the Jobstatuses that tie for the last time for a Job. I have a bit of a quibble with the FirstSortedValue function over how it handles ties.

JobNo	JobStatusDate	JobStatus
GAS-20170115-0232	15/01/2017	CREATE, RECEIVED
GAS-20170115-0232	19/01/2017	ASSIGNED
GAS-20170115-0232	11/02/2017	ACCEPTED
GAS-20170115-0232	28/06/2017	ASSIGNED
GAS-20170115-1400	15/01/2017	CREATE, RECEIVED
GAS-20170115-1400	17/01/2017	ASSIGNED
GAS-20170115-1400	25/05/2017	ASSIGNED
GAS-20170115-1400	07/06/2017	ASSIGNED
GAS-20170115-1400	26/06/2017	CLEARED
GAS-20170115-1400	27/06/2017	DISPOSE

Cheers

Andrew

Re: How to find date wise final status

vengadeshpalani — Thu, 29 Jun 2017 16:05:54 GMT

ya sunny

for status, we can give order no like

Create --> 1

Received -->2

suppose it fall in the same DateTime means we want to check status order no

so for day maximum status order no is 2 result is Received

please check attached file let me know if i'm going in wrong way

Re: How to find date wise final status

sunny_talwar — Thu, 29 Jun 2017 16:09:28 GMT

Isn't this working? Seems like it is working.... can you point out what isn't working?