Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
buzzy996
Master II
Master II

help on expression?

Hi Members,

could u pls help on following query?

my input data:

UpDatedDateDocIDDocStatusTeamClientServiceTypeDocumentTypeProcessorNameProfile
10/24/2013 8:1800004590ProcessedTeam1Client1ServiceTypeDocumentTypeAA
8/1/2013 5:3900004591ProcessedTeam1Client1ServiceTypeDocumentTypeBA
8/1/2013 6:3900004591ProcessedTeam1Client1ServiceTypeDocumentTypeAA
8/2/2013 5:5800004592ProcessedTeam1Client1ServiceTypeDocumentTypeAA
8/2/2013 6:5800004592ProcessedTeam1Client1ServiceTypeDocumentTypeAA
8/3/2013 7:5800004592ERRORTeam1Client1ServiceTypeDocumentTypeCE
8/3/2013 8:5800004592ProcessedTeam1Client1ServiceTypeDocumentTypeBA
8/3/2013 9:5800004592CompletedTeam1Client1ServiceTypeDocumentTypeAA
8/2/2013 6:1100004593ProcessedTeam1Client1ServiceTypeDocumentTypeBA
8/2/2013 6:1400004594ProcessedTeam1Client1ServiceTypeDocumentTypeAA
8/3/2013 6:1700004595ProcessedTeam1Client1ServiceTypeDocumentTypeBA
8/3/2013 6:2000004596ProcessedTeam1Client1ServiceTypeDocumentTypeAA

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

1 Solution

Accepted Solutions
buzzy996
Master II
Master II
Author

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

View solution in original post

5 Replies
buzzy996
Master II
Master II
Author

any help?

simenkg
Specialist
Specialist

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
Not applicable

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

juleshartley
Specialist
Specialist

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

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