Limit Load based on date and Sort results

[mwscott1]

This is kind of a two part question.

1. I have tried limiting my load statement based on results having a completion date >= 5/21/2016. I can not figure out the syntax of the where statement and I am not sure a where statement is the correct option. Blow is my script I removed the where statement.

LOAD [User ID] as AGT_6,
     [User Full Name],
     Role,
     [User Email],
     [User Orig. Hire Date],
     [Training Object ID],
     [Training Title],
     [Training Type],
     [Transcript Status],
     [Transcript Completed Date],
     DATE(Subfield([Transcript Completed Date],' ',1), 'MM/DD/YYYY') as Trans_DATE,
    NUM(Subfield([Transcript Completed Date],' ',1))  AS Trans_DATENUM, 
NUM(Subfield([Transcript Completed Date],' ',1)) - YEARSTART(NUM(Subfield([Transcript Completed Date],' ',1))) + 1 AS Trans_DAYINYEAR,
DAY(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_DAYOFMONTH,
WEEKDAY(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_DAYNAME, 
WEEK(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_WEEK,
MONTH(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_MONTHNAME,  
YEAR(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_YEAR, 
MONTHNAME(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_MONTHYEAR, 
WEEKSTART(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_WEEKSTART,
WEEKDAY(NUM(Subfield([Transcript Completed Date],' ',1))) AS Trans_WEEKDAY

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

2. Also, I already have year, month, day, etc... Is it possible to use subfield to get these additional fields

Quarter (Q1, Q2, Q3, Q4)

Year to Date

Quarter to Date

Month to Date

Last Month

Last Week

Previous Day

Today

[dplr-rn · Accepted solution]

1- Where did you try applying the where clause? 2 options to achive what you want

     a) use where clause in a preceding load not directly from excel

     b) Load directly from excel into a temp table and resident load with where clause

2- Not sure what you mean. Subfield is a text function but i think you already know that. Whats the value in your  [Transcript Completed Date],

check below for ideas for some of your fields

Better Calendar Scripts | Qlikview Cookbook

wrt Quarter to date, month to date etc. what are you trying to achieve? i would use set analysis or The As-Of Table to achieve what i think you want

hope it helps

[mwscott1]

Thank you for the QilikView CookBook link. I am trying to use the code the author provided and I am getting an error at the Get min/max section.

Training:

LOAD [User ID] as AGT_6,

     [User Full Name],

     Role,

     [User Email],

     [User Orig. Hire Date],

     [Training Object ID],

     [Training Title],

     [Training Type],

     [Transcript Status],

     [Transcript Completed Date]

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

//WHERE [Transcript Completed Date] >= '05/21/2016';

//WHERE MATCH(Role,'ADM','ASR','DDM','DGA','PAM','PSM','PSR');

MasterCalendar:

Load

TempDate AS [Transcript Completed Date],

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('[Transcript Completed Date]', recno()))-1 as mindate,

max(FieldValue('[Transcript Completed Date]', recno())) as maxdate

AUTOGENERATE FieldValueCount('[Transcript Completed Date]');

[dplr-rn]

Remove the []s in the fieldvalue call