Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with using a parameter in Load Script

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

11 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   I have answered the related question on this post.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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;

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Just delete this pat of code.

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

          Call DoDir (Dir)

     Next Dir

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Miguel_Angel_Baeyens

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

Miguel_Angel_Baeyens

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Miguel_Angel_Baeyens

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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