How to find date wise final status

[vengadeshpalani]

hi all

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

Result should be like

[sunny_talwar]

Script or front end?

[antoniotiman]

Hi,

see Attachment.

Regards,

Antonio.

[vengadeshpalani]

in script level Sunny

[sunny_talwar · Accepted solution]

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?

[antoniotiman]

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;

[effinty2112]

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

[effinty2112]

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

[vengadeshpalani]

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

[sunny_talwar]

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