topic Re: Multiple CSV Files in QlikView in QlikView

Multiple CSV Files in QlikView

rmdnkevin — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Multiple CSV Files in QlikView

imhappiee — Mon, 02 Jul 2018 15:00:42 GMT

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

Re: Multiple CSV Files in QlikView

BalaBhaskar_Qlik — Mon, 02 Jul 2018 16:51:23 GMT

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;

Re: Multiple CSV Files in QlikView

gireesh1216 — Mon, 02 Jul 2018 17:23:58 GMT

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:

Re: Multiple CSV Files in QlikView

rmdnkevin — Tue, 03 Jul 2018 02:50:21 GMT

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

Re: Multiple CSV Files in QlikView

rmdnkevin — Tue, 03 Jul 2018 02:52:19 GMT

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 ?