topic Re: Concatenate data from files in a folder in QlikView

Concatenate data from files in a folder

swati_rastogi27 — Wed, 22 Feb 2017 06:28:23 GMT

Hi Experts,

I have a folder ..\QVD_DataFiles in which I have monthly revenue files :

Revenue_Details_201401

Revenue_Details_201402

Revenue_Details_201702

All files starting 201401 are refreshed daily , so file create timestamp cannot be used.

Also , every month a new file gets added here.

I want to loop through all files in this folder , but specifying start and end date conditions

Lets say , I don't want data from 2014 files.

In that case ,suppose start_date=201504

End Date will always be current year month (201702)

How to concatenate data , from files , from specified date ranges?

Re: Concatenate data from files in a folder

Wed, 22 Feb 2017 06:42:56 GMT

Try like this loop

t1:

for yeartoload=2015 to 2016

for month= 04 to 12

let vmakedate= makedate($(yeartoload),$(month),1) ;

load *

from Revenue_Details_$(vmakedate) ;

Re: Concatenate data from files in a folder

swati_rastogi27 — Wed, 22 Feb 2017 07:00:37 GMT

The thing is , my end month is always the current month,which i don't want to hardcode.

If I use :

LET $(vEndMonth)=num(Month(Today()), I get '2' , whereas I wanted '02'

Could you help ?

Re: Concatenate data from files in a folder

Anonymous — Wed, 22 Feb 2017 07:19:59 GMT

Use LET $(vEndMonth)=num(Month(Today()),'00'),

Re: Concatenate data from files in a folder

Anonymous — Wed, 22 Feb 2017 07:22:38 GMT

I think the loop only gets the months 04 to 12 every year if used that way. May be we need to loop the incomplete year 1st and the go with Month = 01 to Num(Month(Today()),'00') for remaining years.

Re: Concatenate data from files in a folder

swati_rastogi27 — Wed, 22 Feb 2017 07:31:48 GMT

Since the start month is also not fixed , using inner loop to start with 4 would not be a correct option.

Is there a possibility to loop from 201504 till 201702 ,incrementing yearmonth by 1 each time?

Re: Concatenate data from files in a folder

sasikanth — Wed, 22 Feb 2017 07:41:19 GMT

Try some thing below

SET vPath='D:\Community\VKS\SOURCE';

LET VstartDate=Date#('201402','YYYYMM');

LET vEndDate=num(Date(Today(),'YYYYMM'));

FOR EACH vFile in ('$(vPath)\*.xlsx')

MAINLOAD:

LOAD Distinct

FILENAME() as FileName,

(KeepChar(FileName(),1234567890))AS Datef,

Date(MakeDate(Left((KeepChar(FileName(),1234567890)),4),Right((KeepChar(FileName(),1234567890)),2)),'YYYYMM') As Date1,

FILENAME()&'-'&FileTime() as File_Key

from $(vFile);

NEXT vFile

FINAL:

Load *,FileTime() As FileTime Resident MAINLOAD where num(Date1)>Date#('201402','YYYYMM') and num(Date1)<=num(Date(Today(),'YYYYMM')) ;

Drop Table MAINLOAD;

Re: Concatenate data from files in a folder

Anonymous — Wed, 22 Feb 2017 07:44:32 GMT

Try something like below

for i = Monthstart('01/04/2015') to Monthstart(Today)

Let Suffix = Year($(i))& Num(Month($(i)),'00');

load *

from Revenue_Details_$(Suffix) ;

Re: Concatenate data from files in a folder

swati_rastogi27 — Wed, 22 Feb 2017 08:43:49 GMT

That does not seem to be working .

Its reading the same file multiple times, more than 15 times in fact

Re: Concatenate data from files in a folder

sarvesh — Wed, 22 Feb 2017 09:06:34 GMT

Hi..Swati,

Please refer below mentioned URL, i think it will definitely help you.

Concatenate Load data from multiple files

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: Concatenate data from files in a folder

Wed, 22 Feb 2017 09:14:24 GMT

Create like this

LET $(vEndMonth)=num(Month(Today())) &num(year(Today())) ;

Re: Concatenate data from files in a folder

swati_rastogi27 — Wed, 22 Feb 2017 11:02:26 GMT

This is how I implemented it:

LET vEndYear=Num(Year(Today()),'0000');

LET vStartYear=Num('2014','0000');

LET vDiffYear=$(vEndYear)-$(vStartYear);

for i=0 to $(vDiffYear) step 1

LET vYearBase = $(vStartYear) +$(i);

for j = 1 to 12 step 1

LET vMonthBase = Num($(j),'00');

LET vYearMonth = $(vYearBase)$(vMonthBase);

TRACE 'YearMonth is' $(vYearMonth);

if isnull(filetime('$(lQVDPath)QVD_Revenue_Details_'&$(vYearMonth)&'.qvd')) Then

TRACE ' The file for ' $(vYearMonth);

TRACE 'does not exist';

ELSE

Revenue_Details:

load *

from $(lQVDPath)QVD_Revenue_Details_$(vYearMonth).qvd(qvd) ;

END IF;

next;

STORE Revenue_Details into $(lQVDPath)QVD_MD_Revenue_Details.qvd(qvd);

DROP TABLE Revenue_Details;