Solved: Concatenate data from files in a folder - Qlik Community

swati_rastogi27 · ‎2017-02-22

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?

swati_rastogi27 · ‎2017-02-22

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;

View solution in original post

Report Inappropriate Content · ‎2017-02-22

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

swati_rastogi27 · ‎2017-02-22

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 · ‎2017-02-22

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

Anonymous · ‎2017-02-22

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 · ‎2017-02-22

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 · ‎2017-02-22

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 · ‎2017-02-22

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

swati_rastogi27 · ‎2017-02-22

That does not seem to be working .

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

sarvesh · ‎2017-02-22

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