topic Re: Re: Problem regarding creating QVD.. in QlikView

Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 07:17:22 GMT

Hey,

I am having a problem in creating qvd.

I initially created qvd and opened it with notepad.

Now whatever qvd i am creating it is by default in notepad form. So , Kindly help me What to do because may be qlikview not able to read qvd in notepad form.

Please help.

THanx

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 07:29:10 GMT

Open the Property dialogue box of a QVD file,

Click Change as shown in #1, Use Qlikview as in #2.

This will set the default program to qlikview for opening any QVD file....

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 08:37:30 GMT

Hey thanx.

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 08:46:11 GMT

HEy please solve my this issue also.

I am having problem in incremental load.

I am using excel sheet dummy data stored on desktop. I created qvd file. Now i updated data and want incremental load. I have used following code. Please help me what to do :

Directory;

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

[Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

STORE Disaster into C:\Users\nikhil.garg\Desktop\disaster1.qvd ;

// Incremental load :

SQL SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM 'C:\Users\nikhil.garg\Desktop\Disaster_Data\[Copy of Disaster.xlsx]'

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM C:\Users\nikhil.garg\Desktop\disaster1.qvd

WHERE NOT Exists[Id];

STORE Disaster INTO C:\Users\nikhil.garg\Desktop\disaster1.qvd;

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 08:46:29 GMT

Close the thread by marking it answered..........

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 08:47:49 GMT

Always Start different issues in different threads........

It helps others in checking with similar issues.

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 08:49:36 GMT

Whats the issue in it......

The code seems correct......

If possible post a sample file , it helps in diagnosing the problem easily..

Re: Problem regarding creating QVD..

mdmukramali — Fri, 05 Sep 2014 08:54:20 GMT

Dear ,

in case of Incremental load.

first you need to load the full load and store that table into a Qvd file.

Then comment out the full load.

and perform the incremental load with the where condition and concatenate the full load table which you stored into the Qvd file with where exist condition.

if possible share the sample file

Thanks,

Mukram.

Re: Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 08:59:00 GMT

Please see to it .

I have attached the sample file.

It is giving em error : ODBC connection failed . Cannot find file.

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 09:03:48 GMT

I think you are not using the correct connection string thats why its gving you ODBC Connection failed...

Please check your connection string and the DSN which you are using..

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 09:07:09 GMT

Hey,

I am not putting any dsn name. Niether using ODBC connection. Please help.

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 09:14:46 GMT

Please give me exact Error message

Moreover without ODBC connection how this error is coming???????

Please check you are retrieving data via SQL statement. So you have to connect to a data base via connection string then only a SQL statement can execute..

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 09:18:21 GMT

Hey,

Following 3 errors are coming :

1: ODBC connection failed

SQL ODBC CONNECT32 TO [Excel Files;DBQ=C:\USERS\NIKHIL.GARG\DESKTOP\DISASTER_DATA\Copy of Disaster.xlsx]

2 : ODBC connection failed

Sql SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM 'C:\Users\nikhil.garg\Desktop\DISASTER_DATA\[Copy of Disaster.xlsx]' WHERE ModificationTime >= #2014-09-05 14:12:58#

3: Cannot open file 'C:\Users\nikhil.garg\Desktop\disaster1.qvd WHERE NOT Exists[Id]' The system cannot find the file specified.

Concatenate

LOAD Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM C:\Users\nikhil.garg\Desktop\disaster1.qvd WHERE NOT Exists[Id]

Re: Problem regarding creating QVD..

Fri, 05 Sep 2014 09:44:59 GMT

Hi,

make sure SQL select statement should have object( table ,from which you wish to extract data) at the end of the load statement in the script.

where you are extracting new data ? from SQL or local desktop ?

because your SQL Script is not having correct end tags.please check once.

"SQL SELECT Id, StartDate, EndDate, Country, Type, Sub_Type, Name, Killed, Injured, Year FROM 'C:\Users\nikhil.garg\Desktop\Disaster_Data\[Copy of Disaster.xlsx]'

after FROM keyword script is not having correct destination path.so that you are getting Connection error.

- Charitha.

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 09:48:23 GMT

Hey,

My data (excel sheet) is on local desktop.

Now i have used following by hit and trial now it is giving only 1 error.

Please help me .

Directory;

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

[Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

STORE Disaster into C:\Users\nikhil.garg\Desktop\disaster1.qvd ;

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx] (XUserId is dAHCfQRMNLacWZA, XPassword is QDdXaQRMNLacGMA);

SQL SELECT * FROM `C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx`.`Disaster$`

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year

FROM

disaster1.qvd

(qvd) WHERE NOT Exists(Id);

STORE Disaster INTO C:\Users\nikhil.garg\Desktop\disaster1.qvd;

----------------------------------------------------------------------------------------------------

Now it is giving me following error :

SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

SQL SELECT * FROM `C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx`.`Disaster$`

WHERE ModificationTime >= #2014-09-05 14:12:58#

Please help

Thanx.

Re: Problem regarding creating QVD..

Fri, 05 Sep 2014 09:52:41 GMT

if you are loading from desktop, it should't have SQL keyword.

please ensure that is it correctly done or not

Re: Problem regarding creating QVD..

Fri, 05 Sep 2014 09:54:59 GMT

i can show the difference

from SQL:

LOAD EmployeeCode,

Password,

FirstName,

LastName,

PhoneNumberCode,

EmailAddress,

ManagerId,

ResetPassword,

"RoleIdentifier_ID",

ActiveFlag,

ModifiedDate,

CreateDatetime,

Location;

SQL SELECT *

FROM RM.dbo.Employee;

from Desktop:

LOAD

EmployeeCode,

Password,

FirstName,

LastName,

PhoneNumberCode,

EmailAddress,

ManagerId,

ResetPassword,

"RoleIdentifier_ID",

ActiveFlag,

ModifiedDate,

CreateDatetime,

Location

FROM

INPUT.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

your script is mixed up both.so please correct that.then you wont get connection pblm.

-Charitha

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 09:55:40 GMT

Hey ,

I remove SQL keyword but still same error is there.

SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

SQL SELECT * FROM `C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx`.`Disaster$`

WHERE ModificationTime >= #2014-09-05 14:12:58#

ANd 1 more question :

Do we have to define or declare "ModificationTime" anywhere ??

Re: Problem regarding creating QVD..

Anonymous — Fri, 05 Sep 2014 09:59:23 GMT

I think the ODBC connection that is established between excel file cannot find the file.

Why are you not using the file to directly load it via Table Files button....

Why are you specifically creating an ODBC connection to excel file....

MOREOVER I GOT THE ISSUE.

YOU ARE NOT CREATING THE CONNECTION STRING TO THE PARTICULAR EXCEL FILE .

YOU HAVE DIRECTLY USED THE EXCEL FROM Select BUTTON....

PLEASE SEE THE IMAGE BELOW.

YOU NEED TO HAVE A CONNECTION STRING AND THE THE SQL STATEMENT.

THIS WILL RESOLVE THE PROBLEM....

Re: Problem regarding creating QVD..

nikhilgarg — Fri, 05 Sep 2014 10:00:52 GMT

HEy i ahve resolve the odbc connection problem . But now different problem is there:

SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

SQL SELECT * FROM `C:\Users\nikhil.garg\Desktop\Disaster_Data\Copy of Disaster.xlsx`.`Disaster$`

WHERE ModificationTime >= #2014-09-05 14:12:58#

ANd 1 more question :

Do we have to define or declare "ModificationTime" anywhere ??

Please help.

Thanx.