-1

How can we select the most recent record for the unidata queries. Is there any function or command we can use?

I have data as

Student Term Program Term_Start_date

1, 2018Fall , ABC, 08/01/2018

1, 2018Spring, MATH, 01/01/2018

1, 2017Fall, HIST, 08/01/2017

2, 2017Fall, ENG, 08/01/2017

2, 2017Summer, MATH, 05/01/2017

I want to see the out put as this- Most recent term start date should show per student.

Student Term Program Term_Start_date

1, 2018Fall , ABC, 08/01/2018

2, 2017Fall, ENG, 08/01/2017

I have used the BY.DSND SAMPLE 1 but it gets only one record

Madhu
  • 367
  • 2
  • 7
  • 20
  • What's the @ID of your file (aka table)? Sounds like you need a BREAK.ON with DET.SUP. But Uniquery is not SQL, so it's not going to map 100% to what you want... – Ian McGowan May 05 '18 at 23:57
  • @IanMcGowan Can you help us building the query for this? – Madhu May 10 '18 at 20:49
  • Well, yes, I'm a consultant. But Stack Overflow isn't probably the right place to go down that road. – Ian McGowan May 11 '18 at 05:21

1 Answers1

0

Well outside of writing a UniBasic program to parse the data, you can use an "I or V" type VOC entry to call in concert with the LIST query. Something like this.

DISCLAIMER: Don't make entries or delete entries in your UniData database if you're not comfortable or experienced in UniData. There is NOT an "Undo" button.

Don't enter the quoted text. This is just to highlight what is going on.

AE VOC TEST "TEST or any name that isn't in use"
I "For Insert"
I "I Type entry"
DCOUNT(TERM,@VM); EXTRACT(@RECORD,3,@,0)
PRESS Enter
FI

At this point you have a virtual VOC entry that you can call as a condition to your "LIST" query. Like this.

LIST STUDENT TERM PROGRAM DATE WHEN DATE = TEST

This will return the last multivalued record for the TERM attribute per student. This is assuming that TERM is multivalued and that STUDENT is a singlevalued attribute. How this works is that it's counting the value marks and then returning the record and associated attributes based on the count.

The file has to be set up with single/multivalued combination for the DCOUNT function to be able to count per singlevalued attribute. In this instance a STUDENT can have many TERM's with each TERM having it's own date. Now this isn't a logical sort, it's not ranking dates based on a calendar, but rather based on the order they appear in the file. 

01-01-18
05-01-17
01-01-09 <-- Date Returned

--- First Answer ---

If you're using Uniquery:

LIST CUSTMST ALL BY.DSND @ID SAMPLE 1

Which will sort the file by a value and then just select 1 record.

jthedwalker
  • 16
  • 1
  • Thanks @Jthedwalker.. But it gives only one record.. I mean I have data which has student, terms and programs - I need latest term for that student... – Madhu May 04 '18 at 15:14
  • @MadhuraMhatre Maybe that points you in a closer direction. UniData/UniQuery has it's limitations. Must of the heavy lifting is done with UniBasic. – jthedwalker May 08 '18 at 19:56
  • Hi @Jthedwalker I have added example to my question, can you please help me out? Thanks – Madhu May 09 '18 at 20:40
  • @MadhuraMhatre I'm not sure you noticed but I added to my original answer. You can make a virtual VOC record to count the value marks and use that with WHEN to filter your query. – jthedwalker May 10 '18 at 12:50