Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master II
Master II

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

any help?

Highlighted
Partner
Partner

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.

Highlighted
Specialist
Specialist

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

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

Highlighted
Master II
Master II

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