Help needed with making a pre-calculated table

[Anonymous]

I'm sure it is not that difficult to achieve, but somehow I can't seem to find the solution.

I have a table containing sessions which I want to use to make a pre-calculated table InstalledBase from.

SessionID	DateTime	MonthYear	Status
1	1-9-2011	9-2011	Successful
2	3-9-2011	9-2011	Successful
3	11-10-2011	10-2011	Failed
4	21-11-2011	11-2011	Failed
5	2-12-2011	12-2011	Successful
6	15-12-2011	12-2011	Failed

This should result in something like this:

LastSessionID	LastSuccessfulSessionID	MonthYear
2	2	9-2011
3	2	10-2011
4	2	11-2011
6	5	12-2011

LastSessionID -- the last session for that month

LastSuccessfulSessionID -- last successful session overall until the month

See the code below of what I've got right know. I think the first issue is that I group on monthyear, but what if the LastSuccessful is not in the current month. And also, what if a month had no sessions at al?? This month still should have a lastsuccessful session ID ...

TMS_SessionTerminalConfiguration:
LOAD *
  ,autonumber(SessionTerminalConfiguration.InternalServiceID) as %InternalServiceID
  ;
LOAD SessionTerminalConfiguration.SessionID
    ,SessionTerminalConfiguration.StartDateTime
    ,SessionTerminalConfiguration.EndDateTime
    ,SessionTerminalConfiguration.InternalServiceID
    ,SessionTerminalConfiguration.ConfigurationID
    ,SessionTerminalConfiguration.SessionStatus
    // calculated fields
    ,date(MONTHSTART(SessionTerminalConfiguration.StartDateTime), 'MMM-YYYY') as SessionTerminalConfiguration.StartMonthYear
    ,date(MONTHSTART(SessionTerminalConfiguration.EndDateTime)  , 'MMM-YYYY') as SessionTerminalConfiguration.EndMonthYear
FROM $(vDirectoryTMS)\TMS\SessionTerminalConfiguration.qvd(qvd);
// STEP 1: 
InstalledBase_temp01:
LOAD SessionTerminalConfiguration.InternalServiceID
    ,max(SessionTerminalConfiguration.SessionID)    as SessionTerminalConfiguration.SessionID
RESIDENT TMS_SessionTerminalConfiguration
WHERE SessionTerminalConfiguration.SessionStatus = 'Successful'
GROUP BY SessionTerminalConfiguration.InternalServiceID, SessionTerminalConfiguration.StartMonthYear
;
LEFT JOIN(InstalledBase_temp01)
LOAD *
    ,if(previous(SessionTerminalConfiguration.InternalServiceID) = SessionTerminalConfiguration.InternalServiceID, previous(monthstart(SessionTerminalConfiguration.StartMonthYear)))     as SessionTerminalConfiguration.NextBoekingsmaand
    ;
LOAD SessionTerminalConfiguration.SessionID
    ,SessionTerminalConfiguration.InternalServiceID
    ,SessionTerminalConfiguration.ConfigurationID
    ,SessionTerminalConfiguration.StartDateTime
    ,SessionTerminalConfiguration.StartMonthYear
    ,SessionTerminalConfiguration.EndMonthYear
RESIDENT TMS_SessionTerminalConfiguration
ORDER BY SessionTerminalConfiguration.InternalServiceID,SessionTerminalConfiguration.SessionID DESC;
InstalledBase:
LOAD *
  ,autonumber(InstalledBase.ConfigurationID) as %ConfigurationID
;
LOAD SessionTerminalConfiguration.SessionID                             as InstalledBase.SessionID     
    ,SessionTerminalConfiguration.InternalServiceID                     as InstalledBase.InternalServiceID
    ,SessionTerminalConfiguration.ConfigurationID                       as InstalledBase.ConfigurationID
    ,MONTHSTART(date(addmonths(SessionTerminalConfiguration.StartMonthYear, iterno()-1))) as InstalledBase.Boekingsmaand
RESIDENT InstalledBase_temp01
WHILE  addmonths(SessionTerminalConfiguration.StartMonthYear, iterno()-1) <  if(isnull(SessionTerminalConfiguration.NextBoekingsmaand) = -1, date(monthstart(today()),'MMM-YYYY'),SessionTerminalConfiguration.NextBoekingsmaand)
;
DROP TABLE InstalledBase_temp01;
// STEP 2:
InstalledBase_temp01:
LOAD SessionTerminalConfiguration.InternalServiceID
    ,max(SessionTerminalConfiguration.SessionID)    as SessionTerminalConfiguration.SessionID
RESIDENT TMS_SessionTerminalConfiguration
GROUP BY SessionTerminalConfiguration.InternalServiceID, SessionTerminalConfiguration.StartMonthYear
;
LEFT JOIN(InstalledBase_temp01)
LOAD *
    ,if(previous(SessionTerminalConfiguration.InternalServiceID) = SessionTerminalConfiguration.InternalServiceID, previous(monthstart(SessionTerminalConfiguration.StartMonthYear)))     as SessionTerminalConfiguration.NextBoekingsmaand
    ;
LOAD SessionTerminalConfiguration.SessionID
    ,SessionTerminalConfiguration.InternalServiceID
    ,SessionTerminalConfiguration.ConfigurationID
    ,SessionTerminalConfiguration.StartDateTime
    ,SessionTerminalConfiguration.StartMonthYear
    ,SessionTerminalConfiguration.EndMonthYear
RESIDENT TMS_SessionTerminalConfiguration
ORDER BY SessionTerminalConfiguration.InternalServiceID,SessionTerminalConfiguration.SessionID DESC;
//LEFT JOIN (InstalledBase)
InstalledBase_temp02:
LOAD SessionTerminalConfiguration.SessionID                             as InstalledBase.LastSessionID
    ,SessionTerminalConfiguration.InternalServiceID                     as InstalledBase.InternalServiceID
    ,SessionTerminalConfiguration.StartDateTime                         as InstalledBase.LastSessionStartDateTime
  ,MONTHSTART(date(addmonths(SessionTerminalConfiguration.StartMonthYear, iterno()-1))) as InstalledBase.Boekingsmaand
RESIDENT InstalledBase_temp01
WHILE  addmonths(SessionTerminalConfiguration.StartMonthYear, iterno()-1) <  if(isnull(SessionTerminalConfiguration.NextBoekingsmaand) = -1, date(monthstart(today()),'MMM-YYYY'),SessionTerminalConfiguration.NextBoekingsmaand)
;
DROP TABLE InstalledBase_temp01;
LEFT JOIN (InstalledBase)
LOAD *
  ,if(previous(InstalledBase.InternalServiceID) = InstalledBase.InternalServiceID, interval(InstalledBase.LastSessionStartDateTime - previous(InstalledBase.LastSessionStartDateTime))) as InstalledBase.AantalDagenSindsVorigeCommunicatie
;
LOAD InstalledBase.LastSessionID
    ,InstalledBase.InternalServiceID
    ,InstalledBase.LastSessionStartDateTime
    ,InstalledBase.Boekingsmaand
RESIDENT InstalledBase_temp02
ORDER BY InstalledBase.InternalServiceID, InstalledBase.LastSessionID ASC;
DROP TABLE InstalledBase_temp02;

Hope someone can help me with the mess I made up here 😉

thx in advance

Anita

[swuehl]

Hi Anita,

try this:

Sessions:

LOAD SessionID,

     DateTime,

     MonthYear,

     Status

FROM

[http://community.qlik.com/thread/39158?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

tmpResult:

load lastvalue(SessionID) as LastSession,

MonthYear

resident Sessions group by MonthYear order by DateTime Asc;

left join load lastvalue(SessionID) as LastSuccessfulSession,

MonthYear

resident Sessions where Status = 'Successful' group by MonthYear;

Result:

noconcatenate load MonthYear, LastSession, if(isNull(LastSuccessfulSession),peek('LastSuccessfulSession'),LastSuccessfulSession) as LastSuccessfulSession

Resident tmpResult;

drop table tmpResult;

Hope this helps,

Stefan

edit:

maybe you need a

...

resident Sessions where Status = 'Successful' group by MonthYear order by DateTime Asc;

if your records are not ordered anyway.

And if you have MonthYear without any Sessions, I would start with tmpTable with only MonthYear, spanning the desired range and created by autogenerate then left join the other two tables retrieving the last session and last successful session.