Help needed with making a pre-calculated table

Anonymous — Thu, 17 Nov 2011 12:54:13 GMT

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 *

;

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

;

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

Re: Help needed with making a pre-calculated table

swuehl — Thu, 17 Nov 2011 13:30:14 GMT

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.

topic Re: Help needed with making a pre-calculated table in QlikView

Help needed with making a pre-calculated table

Re: Help needed with making a pre-calculated table