topic Help with using a parameter in Load Script in QlikView

Help with using a parameter in Load Script

Mon, 27 Jun 2011 09:00:43 GMT

Hi guys,

I have an application that runs weekly and I am trying to automate qvd creations and reloads.

My scenario:

I have weekly text file that are run over the weekend and are stored as text files. My file names will always be of the form FileName_DDMMMYYYY for every week. For example week 1 will be 03Jun2011, week 2 will be 10Jun2011, week 3 will be 17Jun2011.

Is there a way to create some kind of dynamic variable in my load script to cater for this changing weekly date value, so that I do not have to hardcode my script always.

At the moment I have come up with

let weekdate = date(today() -2, 'DDMMMYYYY');

let week1 = 'FileName_$(weekdate)';

let j=1;

TableName:

Load ........

From

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

The solution I have is not flexible enough and will only work if I run my automation piece on a monday but not on any other day as the dates change making the file name wrong.

Can someone please help me out to get this working? Thanks

Help with using a parameter in Load Script

kaushiknsolanki — Mon, 27 Jun 2011 09:54:02 GMT

Hi,

I have answered the related question on this post.

http://community.qlik.com/message/126205#126205

Regards,

Kaushik Solanki

Re: Help with using a parameter in Load Script

Mon, 27 Jun 2011 11:21:00 GMT

Hi Kaushik,

Your code is amazing, but I need yourhelp to get it working for just one file in a directory.

I don't want it to look at sub directories at all. I tried making some changes but then the code kept on falling over, because I don't know anything about the kind of scripting you are doing as I am relatively new to scripting in QlikView

Can you please just guide me to make it work by eliminating the For loops so that it just looks for only one file and does not look at sub directories but only the main directory specified.

Thanks

Sub DoDir (Root)

For Each Ext In 'csv' // filetype to search for in current directory

For Each File In FileList (Root & '\MYFILENAME*.' & Ext)

Files:

Load '$(File)' as Name,

FileTime('$(File)') as FileTime,

RangeSum(Peek('FileCount'), 1) as FileCount

Autogenerate 1;

Next File

Next Ext

For Each Dir In DirList (Root & '\*') // search in sundirectories

Call DoDir (Dir)

Next Dir End Sub Call DoDir ('C:\MYFOLDER'); // Starting point

MostRecentFileName:

FIRST 1 LOAD Name AS MostRecentFileName

RESIDENT Files ORDER BY FileTime DESC;

LET vFileToLoad = FieldValueCount('MostRecentFileName');

for each file in FieldValueList('MostRecentFileName')

TableName: LOAD ................... FROM $(file)

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

store TableName into [C\$(file).qvd];

//next file

DROP TABLE MostRecentFileName,Files;

Help with using a parameter in Load Script

kaushiknsolanki — Mon, 27 Jun 2011 11:26:11 GMT

Hi,

Just delete this pat of code.

For Each Dir In DirList (Root & '\*') // search in sundirectories

Call DoDir (Dir)

Next Dir

Regards,

Kaushik Solanki

Re: Help with using a parameter in Load Script

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 11:38:15 GMT

Hello,

Without the need of that macro, and provided all CSV files are in the same folder you can improve the load performance and do just

Table:
LOAD *
FROM C:\Folder\FileName*.txt (options and delimiters here);

The "*" is valid as a name and it will work within the folder specified.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with using a parameter in Load Script

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 11:42:59 GMT

Hi Kaushik,

Although all code that is posted in the Community is free to use and modify, please refer to the actual sources when you provide code to other people. That's the best way to improve the code if needed and check the bugs it may contain and keep the best possible solution in place.

Thanks and regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Help with using a parameter in Load Script

kaushiknsolanki — Mon, 27 Jun 2011 11:48:58 GMT

Hi,

Miquel is you look at the code i have provided, you will see that i have provided the same code.

By the way thanks for suggession.

Regards,

Kaushik Solanki

Re: Help with using a parameter in Load Script

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 12:09:49 GMT

Hello,

Ellaborating a bit further my code above, the following should work fine without needs of macro or loops, and likely improving performance in the loading process

LET vLastDate = Date(Today(), 'YYYYMMDD'); // variable with the date you want to start loading
Table:
LOAD Field1
FROM
file*.txt // filename
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE Date(Right(FileBaseName(), 8)) >= '$(vLastDate)';

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with using a parameter in Load Script

Mon, 27 Jun 2011 12:52:47 GMT

Thanks for you help Miguel. My directory contains multiple files, but I only want to load the file for the most recent date alone, that is why i have avoided using the *.

I tried your code above as I am aiming to avoid using macros and loops if possible, but because of the * it picked up other files with the dates I do not want, as I only want to load the file which has the newest info alone.

Thanks

Re: Help with using a parameter in Load Script

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 13:11:48 GMT

Hi,

Then something like the following may do the trick

DatesOfFiles:
LOAD MaxString(Right(FileBaseName(), 8)) AS PossibleDates
FROM
file*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
LET vLastDate = Peek('PossibleDates', -1);
DROP TABLE DatesOfFiles;
LastDataFile:
LOAD *
FROM file$(vLastDate).txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Although my guess is it does, I don't know whether this would load faster than the macro one.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with using a parameter in Load Script

Mon, 27 Jun 2011 14:17:12 GMT

Thanks a lot for everything

Re: Help with using a parameter in Load Script

Miguel_Angel_Baeyens — Mon, 27 Jun 2011 14:42:35 GMT

Great! You're welcome.

But do not dismiss Kaushik Solanki's suggestion. Specially if you have a lot of files to read. If you test both, please let us know how did they performed.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica