Unique File in a Loop Script

[evansabres]

Hello -

I have a series of files that I am aggregating together into a QVD file. Each file is named YYYY-MM-DD. I have my script set up to create a loop:

FOR EACH vSecMktList IN FILELIST ('*csv')
SecMktTbl:
Load Distinct
Date,
DATE#(SUBFIELD(Date, ' ',1), 'M/D/YY') AS eventDATE,
//left(Date, 9) AS eventDATE,
Opponent,
Section AS tixCODE,
Row,
Price,
Quantity,
[Sample Date],
Board,
ETicket   
FROM $(vSecMktList) (txt, codepage is 1252, embedded labels, delimiter is ',', msq) WHERE Price <=275;
Next vSecMktList;

What I want to do, is to be able to identify each file in my application. Can I identify each file with a unique identifier, i.e name file 2014-11-02 as Day307, so that in my application, I can create an expression to sum the quantity or average the price while referencing Day307, so as to create a table or chart that shows, as of 2014-11-02, the number of tickets for event date YYYY-MM-DD was x?

Thank you.

[#2]

Hi Evan,

you can use your vSecMktList  within each loop to generate a new field yes.

'Day'&DayNumberOfYear(Date#($(vSecMktList),'YYYY-MM-DD')) AS FileDayNumber

Something like that should work for you and generate you a day number based on each file name within the loop

hope that helps

Joe

[marcus_sommer · Accepted solution]

You could use something like:

filebasename() as Source

to identify the files.

- Marcus

[maxgro]

'Day' & num(DayNumberOfYear(date(left(subfield('$(vSecMktList)', '\', -1),10), 'YYYY-MM-DD')), '000') as FileIId