This seems like kind of an odd first real post topic, but it’s something I am working on right now so it’s front of mind and has some pretty good PS Query tips in it.

Background

As part of our participation in the Quality Assurance Program we have a need to upload files to the Department of Education’s online ISIR Analysis Tool.  I created a PS Query to generate the flat data file in the format required by the ISIR Analysis Tool.  In short the format is one line per student including the Social Security Number, the first two characters of the last name, the initial and paid on ISIR transaction numbers, and a ‘Y’ if the student was selected for verification by the school.  We do not supply the ISIR transaction numbers in our file, we have the Analysis Tool use the lowest and highest transactions for comparison, so you will not see those fields in the following logic.

The Logic

The first step is to create a PS Query with whatever selection logic needed to get the correct population of students to submit.  We used a Checklist to track the students selected for verification.  The following expression can then be used to generate the data in the format for the Analysis Tool.

CASE WHEN B.CHECKLIST_STATUS = ‘C’ THEN CONCAT(CONCAT(D.NATIONAL_ID,SUBSTR(E.LAST_NAME_SRCH,1,2)),’    Y’)
ELSE CONCAT(D.NATIONAL_ID,SUBSTR(E.LAST_NAME_SRCH,1,2))
END

The three keys to this logic are the CASE statement, the CONCAT function, and SUBSTR.  The CASE statement works like IF-THEN logic.  If the Checklist Status is ‘C’ then four blank spaces (for the transaction numbers) and a ‘Y’ will be included at the end of the line, otherwise the field will only have the SSN and name code.  The CONCAT function connects two strings together in a single field.  Since the CONCAT function only takes two strings we need to use two of them (nested) in the first CASE.  Finally the SUBSTR function will shorten a string.  You specify the string to shorten, the starting character position, and the number of characters to include.

The Result

The resulting data looks like this:

123456789JE    Y
987654321AB
555555555HI    Y

Use the expression as the only field in the query output and you have a file ready for submission.  Getting familiar with CASE, CONCAT, and SUBSTR is a really good idea too as those are probably the functions that I use the most with PS Query.  If there are any questions, feel free to leave a comment on this post.  Cheers.