Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swati_rastogi27
Creator
Creator

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
Creator
Creator
Author

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;

View solution in original post

11 Replies
Not applicable

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
Creator
Creator
Author

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 ?

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Creator
Creator
Author

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
Master
Master

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;

Anonymous
Not applicable

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

swati_rastogi27
Creator
Creator
Author

That does not seem to be working .

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

sarvesh
Creator III
Creator III

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