Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all
I have data like the following table, I want to show Date wise final status for JobNo
Result should be like
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?
Script or front end?
Hi,
see Attachment.
Regards,
Antonio.
in script level Sunny
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?
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;
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
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
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
Isn't this working? Seems like it is working.... can you point out what isn't working?