This is my log file it showing some error message - cannot open a file ,

[#1]

this is the path - Publisher_Data\EU IT\EID we are given ,can you please why it was not opened ?

[#2]

Hi,

check if the file EID_Employee_Load.qvd is opened and locked by another program....

[jvishnuram]

Hi Nagendra,

Try loading this EID_Employee_Load.qvd file in a separate test file may be some fields are not there which you are mentioned in your script.

[sudeepkm]

Can you pls share the script part. Are you using a Relative path? Are you using the complete host name of the drive?

[#5]

How can we check that is opened or locked ?

[#6]

With task Manager for programs like qvdreader...

and try to restart  Publisher's Service in QMC

[#7]

This is my script

SET Limit_for_active_users = 5;

SET Sessions_Dir='\\eubraqvstest2.euro.mmm.com\Publisher_Data\Vendavo Usage\Session_Dir\';

SET Users_Dir='\\eubraqvstest2.euro.mmm.com\Publisher_Data\Vendavo Usage\Users_Dir\';

SET Security_Dir='\\eubraqvstest2.euro.mmm.com\Publisher_Data\Vendavo Usage\Security_Dir\';

SET Add_Data_Dir='\\eubraqvstest2.euro.mmm.com\Publisher_Data\Vendavo Usage\Add_Data_Dir\';

SET App_Version = 'v1.7';

/* ================================= Load Sessions ================================= */

FOR EACH File in filelist('$(Sessions_Dir)Sessions*.xlsx')

Sessions_1:

    LOAD

        UPPER(Username)                                            AS Session_User,

        [Session creation time]                                    AS Session_Start,

        [Log out time]                                            AS Session_End,

        DATE(MONTHSTART([Session creation time]),'YYYYMMDD')    AS Session_Month

    FROM $(File)(ooxml, embedded labels, table is [tableExport(1)]);

NEXT File;

/* ================================= Load Accounts ================================= */

FOR EACH File in filelist('$(Users_Dir)Users*.xlsx')

Accounts_1:

    LOAD

        UPPER([#"Username"]) & DATE(MONTHSTART(The_Month),'YYYYMMDD')    AS Main_Key_1,

        UPPER([#"Username"])                                            AS User_PIN_1,

        YEAR(MONTHSTART(The_Month))                                        AS Year_1,

        DATE(MONTHSTART(The_Month),'YYYY-MM')                            AS Month_1,

        [Full name]                                                        AS VenUserName_1

    FROM $(File)(ooxml, embedded labels, table is [tableExport(1)])

    WHERE Upper(Valid) = 'TRUE';

NEXT File;

/* ================================= Add Sessions info ============================= */

OUTER JOIN LOAD

    Session_User & Session_Month                                AS Main_Key_1,

    Session_Month                                                AS Session_Moth_1,

    COUNT(Session_User)                                            AS Nbr_sessions_1,

    INTERVAL(SUM([Session_End] - [Session_Start]))                AS Duration_sessions

RESIDENT Sessions_1

GROUP BY Session_User,Session_Month;

DROP TABLE Sessions_1;

/* ================================= Build final Sessions data ================================= */

Sessions:

LOAD

    Main_Key_1                                                                    AS Main_Key,

    User_PIN_1                                                                    AS User_PIN,

    Year_1                                                                        AS Year,

    Month_1                                                                        AS Month,

    VenUserName_1                                                                AS VenUserName,

    IF(Month_1 = DATE(MONTHSTART(ADDMONTHS(TODAY(),-1)),'YYYY-MM'),1,0)            AS Last_Month_Ind,

    IF(Month_1 >= DATE(MONTHSTART(ADDMONTHS(TODAY(),-6)),'YYYY-MM'),1,0)        AS Last_6_Months_Ind,

    Nbr_sessions_1                                                                AS Nbr_sessions,

    Duration_sessions                                                            AS Duration_sessions,

    IF(Nbr_sessions_1 >= $(Limit_for_active_users), 1,0)                        AS Active_User_ind,

    IF(Nbr_sessions_1 < $(Limit_for_active_users) AND Nbr_sessions_1 > 0, 1,0)    AS Occasional_User_ind,

    IF(Nbr_sessions_1 = 0 OR ISNULL(Nbr_sessions_1), 1,0)                        AS Inactive_User_ind

RESIDENT Accounts_1;

DROP TABLE Accounts_1;

/* ================================= Add user info from EID ================================= */

LEFT JOIN LOAD

    Upper(User_Pin)    AS User_PIN,

    Busu_Long_Desc    AS Company,

    Corp_Div_Name    AS Division,

    Dept_Name        AS Department,

    Empt_Stus_Desc    AS Person_status,

    Job_Func_Desc    AS Function,

    Job_Titl_Name    AS Job_title,

    Loc_Desc        AS Location,

    IF(IsNull(Mgmt_Desc) or Mgmt_Desc = '                              ','No Mngmnt',Mgmt_Desc)        AS Management,

    Prsn_Name        AS Name,

    Work_Ctry_Code    AS Work_Ctry_Code,

    Work_Ctry_Name    AS Country

FROM $(UserPath2)EID_Employee_Load.qvd (qvd);

/* ================================= Load additional dimensions ================================= */

Area:

LOAD

    Area,

    Region,

    Work_Ctry_Code

FROM $(Add_Data_Dir)QV_Data.xls (biff, embedded labels, table is Area$);

Business:

LOAD

    BusinessGroup,

    Division

FROM $(Add_Data_Dir)QV_Data.xls (biff, embedded labels, table is Business$);

Function_Group:

LOAD

    Function,

    Function_Group

FROM $(Add_Data_Dir)QV_Data.xls (biff, embedded labels, table is Function$);

[#8]

U r using relative path.

So just check whether some other drive has the same path?

Regards,

Kabilan K.

[#9]

the fields are same in both apps

[#10]

this is the path we are given - $(userpath)-\\05\Publisher_Data\EU IT\EID\

there is no other with this name