topic Re: QVD store command error in QlikView

QVD store command error

Anonymous — Mon, 01 Feb 2016 09:41:23 GMT

Hi all,

I am trying to store a QVD by using store command. EVen though I am giving correct path, it is throwing error.

Here initially I am read one Qvd from the same path and renaming and saving the QVD based on some condition.

Regards,

Kumar

Re: QVD store command error

Colin-Albert — Mon, 01 Feb 2016 09:44:56 GMT

The source table Incmonths is causing the error not the file path.

Does the Incmonths table have any data in it?

Re: QVD store command error

aniketsr — Mon, 01 Feb 2016 09:46:19 GMT

Hi Kumar,

Put the qvd path in the below format

Store Incmonths into [QVD PATH]

Store Incmonths into "QVD PATH"

Re: QVD store command error

Colin-Albert — Mon, 01 Feb 2016 09:46:32 GMT

Can you post more of your script?

Are you loading the source QVD twice - you may need to use "noconcatenate" or rename the fields.

Re: QVD store command error

sasikanth — Mon, 01 Feb 2016 09:53:02 GMT

HI,

Check the table name 'Incmonths' , this might be the issue

Re: QVD store command error

jonathandienst — Mon, 01 Feb 2016 09:53:24 GMT

Comment out the STORE line and open the table viewer after the reload. You will see that IncMonths was not created or was dropped before the STORE command.

If IncMonths had the same fields as another table, the load may have auto-concatenated into the other table, as mentioned by ca above.

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 09:54:16 GMT

Your syntax looks okay..

Just check whether Incmonths exist or not?

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 09:57:18 GMT

Hi All,

This is my script.

Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Reports\Retention\QVD Retention\';

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

Max(Floor(Timestamp#(site_visit_updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as MaxDate

Resident Inserts;

LET vMinMonth = num(AddMonths(Peek('MaxDate'),-13));

DROP TABLE MinMaxDates;

Incmonths:

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime > '$(vMinMonth)';

STORE Incmonths into $(Vqvdpath)fact_site_visit_History.QVD (qvd);

DROP TABLE Incmonths;

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 09:58:16 GMT

My source QVD is having more than 200 million records.

Re: QVD store command error

Colin-Albert — Mon, 01 Feb 2016 10:01:09 GMT

You need to add a NOCONCATENATE as the field names are the same so QlikView will add he rows to the existing Inserts table not create a new table.

Try

Incmonths:

NOCONCATENATE

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime > '$(vMinMonth)';

STORE Incmonths into $(Vqvdpath)fact_site_visit_History.QVD (qvd);

DROP TABLE Incmonths;

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 10:01:21 GMT

use this?

Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Reports\Retention\QVD Retention\';

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

Max(Floor(Timestamp#(site_visit_updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as MaxDate

Resident Inserts;

LET vMinMonth = num(AddMonths(Peek('MaxDate'),-13));

DROP TABLE MinMaxDates;

noconcatenate // as all data is going to Inserts not to Incmonths

Incmonths:

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime > '$(vMinMonth)';

STORE Incmonths into $(Vqvdpath)fact_site_visit_History.QVD (qvd);

DROP TABLE Incmonths;

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 10:02:01 GMT

use noconcatenate before Incmonths

Re: QVD store command error

Mark_Little — Mon, 01 Feb 2016 10:02:56 GMT

Hi,,

As other have suggested, i would take a look at the table Incmonths.

Makes sure there is data in this table, before you are storing it.

Maybe add some traces to see if your variables are being populated correctly as well?

Mark

Re: QVD store command error

avinashelite — Mon, 01 Feb 2016 10:03:57 GMT

I think the variable vMinMonth is not setting properly check the variable values once , AddMonths function will work only on the values which is in the date format so before using this function please check the value once and set the format properly

Re: QVD store command error

krishnacbe — Mon, 01 Feb 2016 10:09:25 GMT

Check whether the format for dates in both the source and variable are same.

Regards

Krishnapriya

Re: QVD store command error

sasiparupudi1 — Mon, 01 Feb 2016 10:26:22 GMT

I think your table may not be getting created. I suggest you to comment the store condition and see if the table is created or not

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

Max(Floor(Timestamp#(site_visit_updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as MaxDate

Resident Inserts;

LET vMinMonth = num(AddMonths(Peek('MaxDate'),-13));

DROP TABLE MinMaxDates;

Incmonths:

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime > '$(vMinMonth)';

//STORE Incmonths into $(Vqvdpath)fact_site_visit_History.QVD (qvd);

//DROP TABLE Incmonths;

hth

Sasi

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 13:55:31 GMT

First I have to say Thanks Colin. It is creating QVD, but it is without records. It doesn't have any records in that QVD.

Re: QVD store command error

Colin-Albert — Mon, 01 Feb 2016 14:03:43 GMT

your where clause is trying to compare a datetime value to a month value.

both sides of the comparison must be the same format.

WHERE site_visit_updated_datetime > '$(vMinMonth)';

Add a trace command on vMinMonth so you can see the format in the logfile.

Trace vMinMonth is $(vMinMonth) ;

Re: QVD store command error

Anonymous — Mon, 01 Feb 2016 14:14:45 GMT

Can you see this screen shot. It is writing value. I have converted site_visit_updated_datetime also Num.

Re: QVD store command error

Mon, 01 Feb 2016 14:20:41 GMT

Try this:

Inserts:

LOAD * FROM

$(Vqvdpath)fact_site_visit.QVD (qvd);

MinMaxDates:

LOAD

Max(Floor(Timestamp#(site_visit_updated_datetime, 'MM/DD/YYYY hh:mm:ss.fff'))) as MaxDate

Resident Inserts;

LET vMinMonth = num(AddMonths(Peek('MaxDate'),-13));

DROP TABLE MinMaxDates;

Noconcatenate

Incmonths:

LOAD *

Resident Inserts

WHERE site_visit_updated_datetime > '$(vMinMonth)';

STORE Incmonths into $(Vqvdpath)fact_site_visit_History.QVD (qvd);

DROP TABLE Incmonths;