Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
swati_rastogi27
Contributor

Concatenate data from files in a folder

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?

1 Solution

Accepted Solutions
swati_rastogi27
Contributor

Re: Concatenate data from files in a folder

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;

next;

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

DROP TABLE Revenue_Details;

11 Replies
Not applicable

Re: Concatenate data from files in a folder

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) ;

next

next

swati_rastogi27
Contributor

Re: Concatenate data from files in a folder

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 ?

laddu_927
Valued Contributor

Re: Concatenate data from files in a folder

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

laddu_927
Valued Contributor

Re: Concatenate data from files in a folder

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.

swati_rastogi27
Contributor

Re: Concatenate data from files in a folder

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?

sasikanth
Valued Contributor III

Re: Concatenate data from files in a folder

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;

laddu_927
Valued Contributor

Re: Concatenate data from files in a folder

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) ;

next

Highlighted
swati_rastogi27
Contributor

Re: Concatenate data from files in a folder

That does not seem to be working .

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

sarvesh
Contributor III

Re: Concatenate data from files in a folder

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