Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a daily file that is created every day.
STORE * FROM DailyCount INTO /$(vToday)_LocationCount.qvd; (Todays is saved as 42527_LocationCount.qvd)
I need to load a rolling 13 months of these daily files and I am not sure how to do that.
Any ideas?
Uhm, yes, my mistake. FileBaseName only works inside a load statement. Try this instead:
MyTable:
LOAD 0 as Dummy Autogenerate 0;
For Each File in FileList('D:\MyProject\QVD\*.qvd')
LET vBaseName = Left(Subfield('$(File)', '\',-1), Len(Subfield('$(File)', '\',-1))-4) ;
LET vFileDate = num#(SubField('$(vBaseName)','_',1))
If $(vFileDate) > num(AddMonths(Today(),-13)) Then
CONCATENATE(MyTable)
LOAD * FROM $(File) (qvd);
EndIf
Next
DROP FIELD Dummy;
Something like this:
MyTable:
LOAD 0 as Dummy Autogenerate 0;
For Each File in FileList('D:\MyProject\QVD\*.qvd')
LET vFileDate = num#(SubField(FileBaseName($(File)),'_',1))
If $(vFileDate) > num(AddMonths(Today(),-13)) Then
CONCATENATE(MyTable)
LOAD * FROM $(File) (qvd);
EndIf
Next
DROP FIELD Dummy;
LET vFileDate = num#(SubField(FileBaseName($(File)),'_',1))
is giving an error...
Script line error:
LET vFileDate = num#(SubField(FileBaseName(C:\MyProject\QVD\05-27-2016_CenterCount.qvd),'_',1))
Any idea why?
Uhm, yes, my mistake. FileBaseName only works inside a load statement. Try this instead:
MyTable:
LOAD 0 as Dummy Autogenerate 0;
For Each File in FileList('D:\MyProject\QVD\*.qvd')
LET vBaseName = Left(Subfield('$(File)', '\',-1), Len(Subfield('$(File)', '\',-1))-4) ;
LET vFileDate = num#(SubField('$(vBaseName)','_',1))
If $(vFileDate) > num(AddMonths(Today(),-13)) Then
CONCATENATE(MyTable)
LOAD * FROM $(File) (qvd);
EndIf
Next
DROP FIELD Dummy;
Awesome!!! Thank you so much that worked.