Multiple CSV Files in QlikView

[rmdnkevin]

Hello, I'm a beginner in QlikView.

I need to input multiple files in Qlik, the example name of the file is KP2018XX (XX as the month).

Here as the overview of the file.

File Name	Email	Name	Status
KP201801	xxx	xxx	xxx
KP201802	xxx	xxx	xxx
KP201803	xxx	xxx	xxx

How do I transform those table into a table like this ?

Month	Year	Email	Name	Status
Jan	2018	xxx	xxx	xxx
Feb	2018	xxx	xxx	xxx
Mar	2018	xxx	xxx	xxx

Any help would be greatly appreciated.

Thanks

[imhappiee · Accepted solution]

Hi

if the fields in all files are same

then load like the following

1st step:

data:

Load *,

Filename() as filename

from c:\data\KP20*.xlsx

........;

try loading an excel file directly and then in the name part just add * to load all files with similar name

2ndstep:

Now as you have file name with the corresponding data

use subfield, Monthname, year functions to derive respective fields

subfield/Left/Right  function to extract the date and year from the filename

Try and let me know if you need more help

Anand

[balabhaskarqlik]

May be like this:

Input:

Load

*,

FileName() as Filename

from KP20*.XLSX;

Temp:

Load

Mid(Filename,3,6) as Year,

Right(Filename,2) as Month,

*

resident Input;

noconcatenate

abc:

load

Year,

Pick(Match(Month,01,02,03,04,05,06,07,08,09,10,11,12),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as MonthName,

*

Resident Temp;

drop tables Input, Temp;

[gireesh1216]

Use below script in back end:

TEST:

LOAD *,

LEFT(replace([File Name],'KP',''),4) AS NEW_YEAR,

Right([File Name],2) AS NEW_MONTH

;

LOAD [File Name],

     Email,

     Name,

     Status

FROM

[https://community.qlik.com/thread/306584]

(html, codepage is 1252, embedded labels, table is @1);

RESULTS:

[rmdnkevin]

I've tried the Left and Right function, but the Right option for the month still doesnt work for me even there's no error message popped up

Thanks

[rmdnkevin]

The Right option for the month still doesnt work for me even there's no error message popped up.

Is there any script beside that to declare the right function ?