help on expression?

[buzzy996]

Hi Members,

could u pls help on following query?

my input data:

UpDatedDate	DocID	DocStatus	Team	Client	ServiceType	DocumentType	ProcessorName	Profile
10/24/2013 8:18	00004590	Processed	Team1	Client1	ServiceType	DocumentType	A	A
8/1/2013 5:39	00004591	Processed	Team1	Client1	ServiceType	DocumentType	B	A
8/1/2013 6:39	00004591	Processed	Team1	Client1	ServiceType	DocumentType	A	A
8/2/2013 5:58	00004592	Processed	Team1	Client1	ServiceType	DocumentType	A	A
8/2/2013 6:58	00004592	Processed	Team1	Client1	ServiceType	DocumentType	A	A
8/3/2013 7:58	00004592	ERROR	Team1	Client1	ServiceType	DocumentType	C	E
8/3/2013 8:58	00004592	Processed	Team1	Client1	ServiceType	DocumentType	B	A
8/3/2013 9:58	00004592	Completed	Team1	Client1	ServiceType	DocumentType	A	A
8/2/2013 6:11	00004593	Processed	Team1	Client1	ServiceType	DocumentType	B	A
8/2/2013 6:14	00004594	Processed	Team1	Client1	ServiceType	DocumentType	A	A
8/3/2013 6:17	00004595	Processed	Team1	Client1	ServiceType	DocumentType	B	A
8/3/2013 6:20	00004596	Processed	Team1	Client1	ServiceType	DocumentType	A	A

Need to calculate Number of documents are Processed first time(DocID:00004592 processed two times in same day need first processed record at 8/2/2013 5.58 as part of count) (based on datetime of UpDatedDate &DocStatus fields) by UpDatedDate,DocID,dOCStatus,Team,Client,ServiceType,DocumentType,ProcessorName and Profile.

Any help? seems to be we can do by using FirstSortedValue function in expression on table level,but not succeeded.

Thank u...

[buzzy996]

any help?

[simenkg]

noconcatenate

TempChangeOrderTable:

load * resident Table order by DocID asc, UpDatedDate asc;

noconcatenate

First update:

load * resident TempChangeOrderTable where previous(DocID) <> DocID;

drop table TempChangeOrderTable, Table; //Table is the original inputtable from data source, so change it both here and in the first load.

Now you will only read the first line for every DocID.

[Anonymous]

what is your expected output for the example in your OP?

[juleshartley]

I'm not 100% clear what you are asking. Do you want to cound how many documents have a single DocStatus for a given day?

If so I would:

- Create a date field that is a day, without time (using floor() or similar)

- Perform an aggr(count()) over DocID and date where DocStatus = Processed

- Add an 'if' clause if you just want to see where there is 1 in the day.

[buzzy996 · Accepted solution]

sol:

exp on dimension tab:=Date(Aggr(FirstSortedValue(UpDatedDate,RecID),DocID),'YYYY/MM/DD HH:MM:SS')

exp on expressions tab: =FirstSortedValue(Total DocID,-UpDatedDate)

thx guys for your help