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: 
canmarroig
Partner - Creator
Partner - Creator

Prefix of a file name took as field

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

14 Replies
tresesco
MVP
MVP

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
Luminary Alumni
Luminary Alumni

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
Specialist II
Specialist II

load *,

left(filename(),4)               as year,

filename()               as               ledger,

from

Year_budget.scv;

regards

premhas

canmarroig
Partner - Creator
Partner - Creator
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Creator
Partner - Creator
Author

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

javier_florian
Creator III
Creator III

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
Partner - Creator
Partner - Creator
Author

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
Luminary Alumni
Luminary Alumni

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.