Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
load *,
left(filename(),4) as year,
filename() as ledger,
from
Year_budget.scv;
regards
premhas
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
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.
No, it doesn't run, i've always the same error: Field not found <Year>
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;
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
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.