Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vengadeshpalani
Creator
Creator

How to find date wise final status

hi all

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

5.png

Result should be like

6.png

1 Solution

Accepted Solutions
sunny_talwar

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?

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

Script or front end?

antoniotiman
Master III
Master III

Hi,

see Attachment.

Regards,

Antonio.

vengadeshpalani
Creator
Creator
Author

in script level Sunny

sunny_talwar

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?

Capture.PNG

antoniotiman
Master III
Master III

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
Master
Master

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-023215/01/2017 
GAS-20170115-023219/01/2017ASSIGNED
GAS-20170115-023211/02/2017ACCEPTED
GAS-20170115-023228/06/2017ASSIGNED
GAS-20170115-140015/01/2017 
GAS-20170115-140017/01/2017ASSIGNED
GAS-20170115-140025/05/2017ASSIGNED
GAS-20170115-140007/06/2017ASSIGNED
GAS-20170115-140026/06/2017CLEARED
GAS-20170115-140027/06/2017DISPOSE

cheers

Andrew

effinty2112
Master
Master

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-023215/01/2017CREATE, RECEIVED
GAS-20170115-023219/01/2017ASSIGNED
GAS-20170115-023211/02/2017ACCEPTED
GAS-20170115-023228/06/2017ASSIGNED
GAS-20170115-140015/01/2017CREATE, RECEIVED
GAS-20170115-140017/01/2017ASSIGNED
GAS-20170115-140025/05/2017ASSIGNED
GAS-20170115-140007/06/2017ASSIGNED
GAS-20170115-140026/06/2017CLEARED
GAS-20170115-140027/06/2017DISPOSE

Cheers

Andrew

vengadeshpalani
Creator
Creator
Author

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?