topic QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this! in QlikView

QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Mon, 08 Dec 2014 13:45:42 GMT

Hi,

To verify the data of a dashboard in Development after our changes we usually copy all the QVD's from Production environment to the Development environment and then we refresh the dashboard in Development with these QVD's to make sure that the data in

the dashboard is matching with Production.

Development: \\GRRTYRTSDDGDFH\QVSTORAGE_DEV\QV\APP\QVD\Sales.qvd

Production: \\TRUTRUSDGEDRUYER\QV\APP\QVD\Sales.qvd

In my case since difference between the Development and Production environment is only the root path. I was trying to create a batch file using the below code to create the copy command for all the files that exists in the dashboard. So when we run the batch

file which was created it would copy all the files from Production to Development.

Since double quotes(") required start and end of the source/destination files. I was trying to add the double quotes but I am getting extra double quotes in the start and end of the source/destination files.

Below is the code which I have written for this. Can someone please help on this?

mytable:
LOAD
'COPY /Y   ' & '"'&Replace(Upper(Discriminator),'\\GRRTYRTSDDGDFH\QVSTORAGE_DEV\QV\','\\TRUTRUSDGEDRUYER\QV\') & '"'&'   ' &'"'&Upper(Discriminator) & '"'as CopyCommand
FROM [\\TRUTRUSDGEDRUYER\QV\APP\QVW\Medical_Sales.qvw](XmlSimple, Table is [DocumentSummary/LineageInfo])
where right(Discriminator,3)='qvd'
;
store * from mytable into myfile.bat (txt);

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Wed, 10 Dec 2014 06:32:54 GMT

Any update from anyone on this?

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Wed, 10 Dec 2014 12:05:51 GMT

I'm not sure if a copy-handle on file-level and then generated per qv-load is an effective and stable way. I think you should use rather tools like robocopy: http://www.wintotal.de/robocopy-der-kopier-gigant/ on folder-level. The link-content is in german but you could translate it with google translator and I'm sure you will find other sites with similar content in english and other languages.

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Wed, 10 Dec 2014 12:16:17 GMT

Thank you! I don't want to use any tools. I would like to create batch file so that it can executed manually so that it can copy the files from source to destination.

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Wed, 10 Dec 2014 13:50:30 GMT

This would work if your data-path haven't spaces and other special chars which requirre quotes for the batch-statement. I don't know if copy worked with unc-path, if not try it with xcopy/robocopy and similar. If you want execute the batch indepently from the qvw remove the execute-statement.

BatchStatement:
Load
'COPY /Y ' & Upper(Discriminator) & ' ' & Replace(Upper(Discriminator),'D:\DATA\','D:\') as [Echo Off]
FROM (XmlSimple, Table is [DocumentSummary/LineageInfo])
where right(Discriminator,3)='qvd';

store BatchStatement into (txt, no labels, delimiter is ';', msq );

sleep 500;

Execute cmd.exe /C "D:\BatchStatement.cmd";

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

Anonymous — Wed, 10 Dec 2014 14:31:18 GMT

Here in this pivot table i m having the issue with the Performance Total it is varying .

It should be Sum([Coperate SLP])*[Performance])

Means (103*106)+(0*111)=109.18 but its displaying as 111 that is the problem over here.

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Wed, 10 Dec 2014 14:55:45 GMT

Hi bunnyqlik,

what had your comment to do with the topic from this post? Please delete this and create a new posting for it.

Anyway, you need to react on null-values, maybe with rangesum() or alt() and for the calculation for the totals you might need aggr() - http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations.

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Thu, 11 Dec 2014 15:00:18 GMT

Thanks Marcus

Data path may have or may not have spaces but it should work in any of the cases. As discussed earlier is there is any other way to create the copy command as per the request. I don't wish to use anyother tool.

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Thu, 11 Dec 2014 15:22:24 GMT

I don't understand what your use-case is? Like above mentioned I would prefer a copy or synchro on folder-level to reach a rather long-term solution. Tools like robocopy are very flexible to include/exclude folder/files/extensions and so on.

Within qv I would rather load a list of qvw's (per inline or filelist-statement) and store whose source-qvd's in a txt-file which would be read in a loop from a batch- or vbs-file for further actions.

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Mon, 15 Dec 2014 07:44:04 GMT

My use case was to create a copy command for all the QVD's that has been used in the QVW using QV program instead of using any tools.

As you have explained above how this can be taken care in QV. Can you share some sample application on how this can be achieved? Please help!

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Mon, 15 Dec 2014 08:19:00 GMT

To read files witin a loop is in HELP a good example available (for each ... --> dirlist/filelist). To copy from a txt-file see here: http://stackoverflow.com/questions/6257948/batch-copy-a-list-txt-of-files

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Tue, 16 Dec 2014 12:46:44 GMT

I already have list of the QVD's which need to be copied so I don't need any loop now. Only issue which I have mentioned in my first post was the extra double quotes which I am getting. If I fix that my code looks good. Hope my requirement is understandable now.

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

marcus_sommer — Tue, 16 Dec 2014 13:03:20 GMT

If you used another delimiter as comma and avoids any quotes inside the output you won't be have extra double-quotes. There will be no other way within qv. But you could run another batch which could clean/prepare your first batch-file.

- Marcus

Re: QVW to create bat file which contain the copy command for all the QVD's used in the QVW. Help on this!

qlikviewforum — Thu, 18 Dec 2014 08:19:44 GMT

Thanks Marcus... I will try this thing out and will update you.... Thanks for all your help!