topic Script Issue in QlikView

Script Issue

Fri, 11 Mar 2011 10:38:53 GMT

Hi all.

I have a bit of an issue. I am running a QVW on a server that reloads automatically each day. What happens is data gets imported into a directory and is loaded into the QVW and is then archived into a seperate directory (Moved). So what I need to do is set up a script that will load the data only if it is all there. Example:

Separate excel sheets called Transactions, Clients, Area, and Product for example. If i get data for Transactions,Clients and Product but not Area, I want the script to skip loading the section that reloads the Area Information. It still needs to bring in the rest of the data though. I have tried the following but unfortunately it is not working:

DailyFileCheck:

LOAD DailyFileName

FROM '...\Dir'

(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

LET vProcessed = Date(Date#(Mid(peek('DailyFileName',0,DailyFileCheck),13,8),'YYYYMMDD'),'DD/MM/YYYY');

LET vToday = Date(Today()-1);

SET ErrorMode = 2;

exit Script when vProcessed <> Today()-1;

So if anyone has any suggestions or any ideas, please let me know.

Regards.

Script Issue

Fri, 11 Mar 2011 11:33:22 GMT

Anyone have any suggestions?

Script Issue

Fri, 11 Mar 2011 11:59:29 GMT

Hi,

I have an application with a kind of historic of the data. When it is reloaded, for each file I check if it exists and if it has already been loaded, and then store the result in a qvd. If the file does not exist I just load the date from the qvd that was created during the last refresh.

Maybe a little more complex than your request, but I hope it can help you.

set loadHistoryQvdPath='loadHistory.qvd';
set fileExt='.txt';
set qvdExt='.qvd';

let loadDate=now();

let hasLoadHistoryQvd = isNull(FileTime('$(loadHistoryQvdPath)'));

if $(hasLoadHistoryQvd) = -1 then
trace Load History Qvd not found;
loadHistory:
load * inline [
fileName, fileDate, loadDate
];
else
trace Loading History Qvd;
loadHistory:
load
fileName,
fileDate,
loadDate
from
$(loadHistoryQvdPath)
(qvd);
endif

sub loadFile(fileName)

let hasQvd = isNull(FileTime('$(fileName)$(qvdExt)'));

if $(hasQvd) = -1 then
trace qvd file $(fileName)$(qvdExt) not found;
else
trace qvd file $(fileName)$(qvdExt) ok;
load *
from $(fileName)$(qvdExt)
(qvd);

end if

let hasFile = isNull(FileTime('$(fileName)$(fileExt)'));

if $(hasFile) = -1 then
trace file $(fileName)$(fileExt) not found;
else
let fileDate = FileTime('$(fileName)$(fileExt)');
trace file $(fileName)$(fileExt) ok, date : $(fileDate);

tmp:
load loadDate resident loadHistory where fileName = '$(fileName)' and fileDate = '$(fileDate)';

if NoOfRows('tmp') > 0 then
trace file has already been loaded;
else
trace load file;

$(fileName):
load
'$(loadDate)' as $(fileName)Date,
*
from $(fileName)$(fileExt)
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

load * inline [
fileName, fileDate, loadDate
$(fileName), $(fileDate), $(loadDate)
];

store $(fileName) into $(fileName)$(qvdExt);

end if
drop table tmp;
end if

end sub;

call loadFile('file1');
call loadFile('file2');
call loadFile('file3');

store loadHistory into $(loadHistoryQvdPath);