Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
varmekontrol
Creator
Creator

Load files greater than date, depending on content in filename

Hi Everybody 

I want to only load files greater than the last 7 days. 
Only thing I have to go from is the file name ->  0012109388_00012813_valuereport_20190128005156_2105.csv

I need to load all files from the folder were dates are greater than 20190121. 

Tried something like

let vEndDate = text(date((Today()-7), 'YYYY-MM-DD hh:mm:ss'));

Data:
LOAD @1 as "Cme No", 
     @2 as "Meter No",  
     @3 as Created, 
     @5 as "Cmi No", 
     @7 as "Radio Signal", 
     @8 as Temp, 
     @9 as Humidity     
FROM 
[Filpath\Database\$(vEndDate)_2105.csv]
(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines)
where @2<99999 and @8<60;

But get an error

Labels (1)
1 Solution

Accepted Solutions
Somasundaram
Creator III
Creator III

 

Use the below,

let vEndDate = text(date((Today()-7), 'YYYY-MM-DD hh:mm:ss'));

Data:
LOAD @1 as "Cme No", 
     @2 as "Meter No",  
     @3 as Created, 
     @5 as "Cmi No", 
     @7 as "Radio Signal", 
     @8 as Temp, 
     @9 as Humidity     
FROM 
[Filpath\Database\ 0012109388_00012813_valuereport_$(vEndDate)_2105.csv]
(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines)
where @2<99999 and @8<60;

 

 


-Somasundaram

View solution in original post

3 Replies
Somasundaram
Creator III
Creator III

 

Use the below,

let vEndDate = text(date((Today()-7), 'YYYY-MM-DD hh:mm:ss'));

Data:
LOAD @1 as "Cme No", 
     @2 as "Meter No",  
     @3 as Created, 
     @5 as "Cmi No", 
     @7 as "Radio Signal", 
     @8 as Temp, 
     @9 as Humidity     
FROM 
[Filpath\Database\ 0012109388_00012813_valuereport_$(vEndDate)_2105.csv]
(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines)
where @2<99999 and @8<60;

 

 


-Somasundaram

View solution in original post

MindaugasBacius
Partner
Partner

 

SET ErrorMode = 0;

SUB DoDir(Root)

FOR each File in filelist( Root & '\*.csv')

TRACE '$(File)';

IF Left(Textbetween('$(File)', '_valuereport_', '_2105.'), 😎 > Num(Date(Today() - 8)) THEN

 Data:
    LOAD @1 as "Cme No"
     ,@2 as "Meter No"
     ,
    FROM
    [$(File)]
    (txt, codepade is 1252, no labels, delimiter is ';', msq, header is 1 lines)
where @2<99999 ans @8<60; NEXT File END IF FOR each Dir in Dirlist (Root&'\*') CALL DoDir(Dir) NEXT Dir END SUB CALL DoDir('Filpath\Database\') SET ErrorMode = 1;

Well something like above.

 

varmekontrol
Creator
Creator
Author

HI

Thank you for that. 
Forgot to mention, that I have different files that I would like to load.

They are all identical in format. But like the date, they can differ. 

0012109388_00012813_valuereport_20190128005156_2105.csv

XXXXXXXXX_XXXXXXX_XXXXXXX_201901280051256_2105.csv

I need to skip to the date part 🙂