Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is my Script
Trace Pick up the Topsecret users, their resources and profiles, and the resources the Profiles use;
TSUserLoad:
Load
@1 as TS_User_Key,
@2 as TS_User,
@3 as TS_Name,
@4 as TS_Type,
@5 as TS_Opid,
@6 as TS_TSO_Prefix,
@7 as TS_Create_Date,
@8 as TS_Modify_Date,
@9 as TS_Last_Date,
@10 as TS_Last_CPU,
@11 as TS_Last_Facility,
@12 as TS_Attributes,
@13 as TS_Expiration_Pswd,
@14 as TS_Pswd_Duration,
if(trim(@14)='','YES','NO') as TS_No_Expired_Pswd,
FileTime() as Filetime_TS,
'EU' as TS_Environ
FROM "$(TopsPath)$(MVS)_tssacc1.txt" (ansi, txt, delimiter is '\t', no labels);
// Pull out the FileTime stamp and store it associated with the environment (left over from multiple TopSecret days)
FileTimeMVS:
load Distinct
TS_Environ
,max(Filetime_TS) as Filetime_TS
Resident TSUserLoad
Group By TS_Environ ;
TSUser:
Load Distinct
TS_User_Key,
TS_User as TS_User_Pin ,
trim(TS_User) as User_PIN,
left(TS_User,2) as TS_User_Prefix,
TS_Name as TS_User_Name,
TS_Type as TS_User_Type,
TS_Opid as TS_User_Opid,
TS_TSO_Prefix,
TS_Create_Date,
TS_Modify_Date,
TS_Last_Date,
if(TS_Last_Date>0,today()-TS_Last_Date) as TS_User_UnusedDays,
TS_Last_CPU,
TS_Last_Facility,
TS_Environ,
TS_Expiration_Pswd,
TS_Pswd_Duration,
TS_No_Expired_Pswd,
'Yes' as TS_User
Resident TSUserLoad
where TS_Type <> 'PROFILE'
and TS_Type <> 'DEPT'
and TS_Type <> 'ZONE'
and TS_Type <> 'GROUP'
and TS_Type <> 'DIVISION' ;
// The above list may change over time - These are NOT Topsecret PIN based "users"
// Break down the user attributes - there can be multiples concatenated together
TSUserAttributes:
left keep Load Distinct
TS_User_Key,
subfield(TS_Attributes,';') as TS_Attribute
Resident TSUserLoad
where len(trim(TS_Attributes)) > 0;
// Now take the non-users and build a table of Profiles
TSProfile:
Load Distinct
TS_User_Key as TS_Profile_Key,
TS_Environ as TS_Profile_Environ,
TS_User as TS_Profile ,
TS_Name as TS_Profile_Name,
TS_Type as TS_Profile_Type,
TS_Opid as TS_Profile_Opid,
TS_TSO_Prefix as TS_Profile_TSO,
TS_Create_Date as TS_Profile_Create,
TS_Modify_Date as TS_Profile_Modify,
TS_Attributes as TS_Profile_Attribs
Resident TSUserLoad
where TS_Type = 'PROFILE'
or TS_Type = 'DEPT'
or TS_Type = 'ZONE'
or TS_Type = 'GROUP'
or TS_Type = 'DIVISION' ;
Drop Table TSUserLoad;
// Now load the Profiles used by Users
TSUserProfileLoad:
Load
@1 as TS_User_Key,
@2 as TS_Profile_Key,
@3 as TS_Profile_ID
FROM "$(TopsPath)$(MVS)_tssacc5.txt" (ansi, txt, delimiter is '\t', no labels);
// Now load the Resources used by Users & Profiles - there can be multiple systems against a resource
TSResourcesLoad:
Load
@1 as TS_User_Profile_Key,
@2 as TS_Owner_Key,
@3 as TS_Resource,
@4 as TS_Resource_Name,
@5 as TS_Access,
@6 as TS_Systems,
subfield(@6,';') as TS_System
FROM "$(TopsPath)$(MVS)_tssacc9.txt" (ansi, txt, delimiter is '\t', no labels);
//Now match the resources to both the real Users (PINs) and the Profiles - using Left Keep
TSUserResources:
left keep (TSUser)
Load Distinct
TS_User_Profile_Key as TS_User_Key
,TS_Owner_Key as TS_User_Owner
,TS_Resource as TS_User_Resource
,TS_Resource_Name as TS_User_Resource_Name
,TS_Access as TS_User_Resource_Access
,TS_Systems as TS_User_Resource_System
,TS_System as TS_User_System
,if(right(trim(TS_System),4)='CICS',mid(TS_System,3,2),'YY') as TS_User_Stage
,if(right(trim(TS_System),4)='CICS',mid(TS_System,1,2),'XX') as TS_User_Region
Resident TSResourcesLoad;
TSProfileResources:
left keep (TSProfile)
Load Distinct
TS_User_Profile_Key as TS_Profile_Key
,TS_Owner_Key as TS_Profile_Owner
,TS_Resource as TS_Profile_Resource
,TS_Resource_Name as TS_Profile_Resource_Name
,TS_Access as TS_Profile_Resource_Access
,TS_Systems as TS_Profile_Resource_System
,TS_System as TS_Profile_System
,if(right(trim(TS_System),4)='CICS',mid(TS_System,3,2),'YY') as TS_Profile_Stage
,if(right(trim(TS_System),4)='CICS',mid(TS_System,1,2),'XX') as TS_Profile_Region
Resident TSResourcesLoad;
// Add TS Users to PIN_CONTROL
Concatenate (PIN_Control) Load Distinct
User_PIN
,left(User_PIN,8) as User_PIN_Key
,left(User_PIN,8) as User_PIN_Nbr
Resident TSUser;
Hi,
While loading TSUserLoad you can add a condition; assuming you'd want last week on T_Last_Date:
Where @9>Today()-7
If T_Last_Date is a date and not a timestamp, the first load could be made even more efficient following techniques of this thread: QlikTip#: Keep Optimized QVD Load
But I see that later, you're looking for TS_User_UnusedDays. Does it fit with loading only last week?
Yes I need only this week data not for last week this is some of the data in TSUser table.
TS_Create_Date | TS_Modify_Date | TS_Last_Date | Filetime_TS |
24/01/2006 | 10/2/2006 | 1/18/2014 | |
24/01/2006 | 15/03/2006 | 1/18/2014 | |
24/01/2006 | 12/6/2007 | 1/18/2014 | |
24/01/2006 | 12/6/2007 | 24/08/2007 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 9/3/2006 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 8/1/2009 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 12/12/2009 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 10/9/2010 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 31/01/2011 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 19/02/2011 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 7/5/2011 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 22/09/2011 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 1/10/2011 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 21/01/2012 | 1/18/2014 |
24/01/2006 | 13/06/2007 | 25/03/2012 | 1/18/2014 |