topic Re: This is my log file it showing some error message - cannot open a file , in QlikView

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

Fri, 31 Jan 2014 09:01:01 GMT

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

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

Fri, 31 Jan 2014 09:02:19 GMT

Hi,

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

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

jvishnuram — Fri, 31 Jan 2014 09:06:14 GMT

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.

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

sudeepkm — Fri, 31 Jan 2014 09:11:32 GMT

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

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

Fri, 31 Jan 2014 09:21:43 GMT

How can we check that is opened or locked ?

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

Fri, 31 Jan 2014 09:29:23 GMT

With task Manager for programs like qvdreader...

and try to restart Publisher's Service in QMC

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

Fri, 31 Jan 2014 09:33:37 GMT

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$);

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

Fri, 31 Jan 2014 09:34:21 GMT

U r using relative path.

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

Regards,

Kabilan K.

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

Fri, 31 Jan 2014 09:37:46 GMT

the fields are same in both apps

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

Fri, 31 Jan 2014 09:43:33 GMT

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

there is no other with this name

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

sudeepkm — Fri, 31 Jan 2014 10:41:51 GMT

Can you pls check this variable's value $(UserPath2)

used in this line

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

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

sudeepkm — Fri, 31 Jan 2014 11:00:19 GMT

in your log this path shows like this:

'\\ukbraa05\Publisher_Data\EU IT\EID\EID_Employee_Load.qvd'

Can you pls verify and make sure that the value of $(UserPath2) is

'\\ukbraa05\Publisher_Data\EU IT\EID\

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

Fri, 31 Jan 2014 11:30:46 GMT

Thanks for all ,My problem is solved

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

regowins — Fri, 14 Mar 2014 19:01:53 GMT

Hi - what did you do to resolve this issue? I am getting the same error when trying to access a file share using a UNC path during a task reload from QMC. It is not a complicated script just a single xls file.

Thanks!