Prefix of a file name took as field

[canmarroig]

Hi, i'm loading a certain number of csv files from different dept, referred to budget, and i want to put YEAR in the name of file, in order to extract and use referred amount to analyse different KPIs (ex: YEAR_filename.csv). Every reload i will have to reload different files, 1 for every dept, 1 for every year. Which function in script can help me?

Tks

[tresesco]

Hope this would help you:

FOR Each a in 1,3,7,'xyz'

LOAD * FROM file$(a).csv;

NEXT

// list all QV related files on disk

SUB DoDir (Root)

FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'

FOR Each File in filelist (Root&' \*.' &Ext)

LOAD

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

NEXT File

NEXT Ext

FOR Each Dir in dirlist (Root&' \*' )

call DoDir (Dir)

NEXT Dir

ENDSUB

CALL DoDir ('C:')

Or, for a simpler solution hint :

For Each year in 2013, 2012  // hard coded the possible years

LOAD
  A,
  B,
'$(year)' as Year               

FROM
C:\Users\....\Documents\tres_$(year).xlsx
(ooxml, no labels, table is Sheet1);
Next     // Edit: Corrected

[jagan]

Hi,

If you get the filename in script

Try like this 

Directory;
LOAD SalesYear,
Sales,
FileName(),
Left(FileName(), 4) AS FileYear
FROM
2013_test.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FileName() will give you the name of the file reloading.

Regards,

Jagan.

[preminqlik]

load *,

left(filename(),4)               as year,

filename()               as               ledger,

from

Year_budget.scv;

regards

premhas

[canmarroig]

I've tried:

For each PRODBDG_file in FileList('..\External Data\XLS\*PRODBDG.xls')

PRODBGD_READ:

LOAD

    DEPT

     MARKET_CD,

     MONTH,

     LEFT(FileName(), 4) as YEAR,

     sum(PCS) as PCS,

FROM "$(PRODBDG_file)"   (biff, embedded labels, table is [budget Production$])

group by

     DEPT,

     MARKET_CD,

     MONTH,

     YEAR;

where i'm doing wrong? Tks

[jagan · Accepted solution]

Hi,

Instead of For loop try this

PRODBGD_READ:

LOAD

    DEPT

     MARKET_CD,

     MONTH,

     LEFT(FileName(), 4) as YEAR,

     sum(PCS) as PCS,

FROM [..\External Data\XLS\*PRODBDG.xls]  (biff, embedded labels, table is [budget Production$])

group by

     DEPT,

     MARKET_CD,

     MONTH,

     YEAR;

Hope this helps you.

Regards,

Jagan.

[canmarroig]

No, it doesn't run, i've always the same error: Field not found <Year>

[javier_florian]

Hi Canmarroig,

Try with this...

Temp_Sheets:

LOAD * INLINE

[Sheets

1,

2,

3];

FOR i=0 TO NoOfRows('Temp_Sheets')-1

LET vFileName = '';

LET vSheetName = purgeChar(peek('Sheets', i, 'Temp_Sheets'), chr(39));

Initial_Data:

LOAD a,

    b,

    c

FROM

$(vFileName)

(ooxml, embedded labels, table is $(vSheetName));

NEXT

DROP TABLE Temp_Sheets;

[canmarroig]

Sorry, maybe you're suggesting me to create a temp sheet made of YEAR, DEPT, ....IN A LOAD INLINE? I don't understand very well your suggestion. Tks

[jagan]

Hi,

Try this

PRODBGD_READ_Temp:

LOAD

    DEPT

     MARKET_CD,

     MONTH,

     LEFT(FileName(), 4) as YEAR,

PCS

FROM [..\External Data\XLS\*PRODBDG.xls]  (biff, embedded labels, table is [budget Production$]);

PRODBGD_READ:

   LOAD

DEPT

     MARKET_CD,

     MONTH,

     YEAR,

sum(PCS) as PCS

RESIDENT  PRODBGD_READ_Temp

group by

     DEPT,

     MARKET_CD,

     MONTH,

     YEAR;

DROP TABLE PRODBGD_READ_Temp;

Hope this helps you.

Regards,

Jagan.