Problem regarding creating QVD..

[nikhilgarg]

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

[nikhilgarg]

ANd NOw my code is ::

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 ;

//Connect to odbc and incremental load :

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;

But following error is coming:

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

[#3]

if it is static, then you can write like this on condition

Load * from

c/----- where ModificationTime >= '2014-09-05 14:12:58';

if it is a dynamic field ,Yes you have to declare a  variable and it will holds the date .

i am attaching file related to incremental load ,check this once.

[#4]

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.

- Charitha.

[Anonymous]

Is your excel file consisting of "ModificationTime " field or not....

And why are you having "#" while defining the date...........

[#6]

please refer this file.

[nikhilgarg]

Hey,

I did not have Modification Time as a field.

ANd # is not created by me . IT is the value of LastExcTime. My code is :

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

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

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

WHERE ModificationTime >= #$(LastExecTime)#; (ALthough i will remove both #)

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;

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

Error coming is :

SQL##f - SqlState: 37000ႀ, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'ModificationTime >= 2014-09-05 14:12:58'.

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

[nikhilgarg]

HEy,

THe lastexecution time is dynamic so i have used "LastExecTime" and comparing it with Modification time. It is like this :

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

WHERE ModificationTime >= $(LastExecTime);

But i want to ask , do i have to declare or define "ModificationTime" or it is predefined ??

[#9]

it should be predefined ,because you are trying to filter records based on existing ModificationTime field in the datasource on LastExecTime

[Anonymous]

First of all you need to have ModificationTime field in your file other you cannot apply where condition on it.

Moreover

the date format of ModificationTime and the date is parameter should match.

if ModificationTime is a timestamp,

then where ModificationTime >=

$(Timestamp(  MakeDate(2014,9,5)& ' '&MakeTime(14,12,58),'M/D/YYYY h:mm:ss[.fff] TT')) should be the condition

[nikhilgarg]

Hey,

Thanx for the help. But i resolved the issue.

I used table files option to load the excel file . Now it is working fine.

I used it like this :

Directory;

LOAD Id,

     StartDate,

     EndDate,

     Country,

     Type,

     Sub_Type,

     Name,

     Killed,

     Injured,

     Year

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

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;

THanx.