Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

canmarroig
Contributor

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

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Prefix of a file name took as field

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.

14 Replies
MVP
MVP

Re: Prefix of a file name took as field

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

MVP & Luminary
MVP & Luminary

Re: Prefix of a file name took as field

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
Valued Contributor II

Re: Prefix of a file name took as field

load *,

left(filename(),4)               as year,

filename()               as               ledger,

from

Year_budget.scv;

regards

premhas

canmarroig
Contributor

Re: Prefix of a file name took as field

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

MVP & Luminary
MVP & Luminary

Re: Prefix of a file name took as field

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
Contributor

Re: Prefix of a file name took as field

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

javier_florian
Contributor III

Re: Prefix of a file name took as field

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
Contributor

Re: Prefix of a file name took as field

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

MVP & Luminary
MVP & Luminary

Re: Prefix of a file name took as field

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.

Community Browser