MAX Date Import Script

DanielP065 — Thu, 15 Apr 2021 22:50:05 GMT

Hello,

I've got this script below that combines 5 tables into one to get yearly Assessment information for students. However I'm having trouble figuring out where and/or how to use the Max (Date) function to only return the most recent record entered for each person?

Any help is appreciated!

Daniel

STUDENTS:
LOAD
'7/1/'& YEAR(IF(num(Month(ENTRYDATE)>6),ENTRYDATE,AddYears(ENTRYDATE,-1))) AS FY_START,
YEAR(IF(num(Month(Today())>6),ENTRYDATE,AddYears(Today(),-1))) & '-' & YEAR(IF(num(Month(Today())>6),ENTRYDATE,Today())) AS SchoolYr,
YEAR(IF(num(Month(ENTRYDATE)>6),AddYears(ENTRYDATE,1),ENTRYDATE)) AS School_Year,

ENROLL_STATUS AS Enroll_Stuatus_S,
Pick(WildMatch(ENROLL_STATUS, -1, 0, 1, 2, 3), 'Preregister', 'Active', 'Inactive', 'Exited', 'Graduated')AS [ENROLL_STATUS_L],

DCID AS STUDENTSDCID,
ID AS STUDENTSID,
LASTFIRST,
STUDENT_NUMBER,
SCHOOLID AS CUR_SCHOOLID_S,

GRADE_LEVEL AS CUR_GRADE_LEVEL,
if(match([GRADE_LEVEL],-2,-1), 'PRE-K',
if(match([GRADE_LEVEL],0,1,2,3,4,5), 'ELEMENTARY',
if(match([GRADE_LEVEL],6,7,8),'MIDDLE',
if(match([GRADE_LEVEL],9,10,11,12),'HIGH',
if(match([GRADE_LEVEL],99),'GRAD'))))) AS [CURRENT_SCHOOL_LEVEL],
Date(Floor(ENTRYDATE),'MM/DD/YYYY') AS ENTRYDATE,

GENDER AS GENDER_S,
Pick(WildMatch(GENDER, 'M', 'F'),'Male','Female') AS [GENDER_L],
ETHNICITY AS ETHNICITY_S,
Pick(WildMatch(ETHNICITY, 'A', 'B', 'C','H', 'I', 'P'), 'Asian', 'Black', 'Caucasian', 'Hispanic', 'Native American', 'Pacific Islander')AS [ETHNICITY_L]

FROM [lib://QVD Store/STUDENTS.qvd]
(qvd)

WHERE ENTRYDATE >= ('7/1/'& YEAR(IF(num(Month(Today())>6),ENTRYDATE,AddYears(Today(),-1)))) AND NOT MATCH (LAST_NAME,'Test','TEST') AND SCHOOLID <> '900' ;

INNER JOIN

LOAD
ID AS TESTID,
NAME AS TEST_NAME,
DESCRIPTION AS TEST_DESCRIPTION
FROM [lib://QVD Store/TEST.qvd]
(qvd)
WHERE NAME = 'AccessELL';

INNER JOIN

LOAD
ID AS STUDENTTESTID,
STUDENTID AS STUDENTSID,
TESTID,
DATE (TEST_DATE, 'MM-DD-YYYY') AS STUDENTTEST_DATE,
SCHOOLID AS TEST_SCHOOLID_S,
GRADE_LEVEL AS STUDENTTEST_GRADELEVEL,
TERMID AS STUDENTTEST_TERMID
FROM [lib://QVD Store/STUDENTTEST.qvd]
(qvd);

INNER JOIN

LOAD
ID AS TESTSCOREID,
TESTID,
NAME AS TESTSCORE_NAME,
DESCRIPTION AS TESTSCORE_DESCRIPTION

FROM [lib://QVD Store/TESTSCORE.qvd]
(qvd)
WHERE NAME = 'Proficiency Level';

INNER JOIN

LOAD
STUDENTID AS STUDENTSID,
STUDENTTESTID,
TESTSCOREID,
NUMSCORE,
PERCENTSCORE,
ALPHASCORE
FROM [lib://QVD Store/STUDENTTESTSCORE.qvd]
(qvd);

Re: MAX Date Import Script

Dalton_Ruer — Tue, 20 Apr 2021 19:58:42 GMT

Check out the FirstSortedValue function and note that you can use - for sort weight to get max instead of min:

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggregationFunctions/firstsortedvalue.htm

topic MAX Date Import Script in App Development

MAX Date Import Script

Re: MAX Date Import Script