Dear Sir,
I facing some issue
i loaded multiple invoice excels into single load
every excel having same table but invoice data is different
in excel names like this
01-04-2019 |
01-05-2019 |
01-06-2019 |
02-04-2019 |
02-05-2019 |
03-04-2019 |
03-05-2019 |
03-06-2019 |
04-04-2019 |
04-05-2019 |
04-06-2019 |
05-04-2019 |
05-06-2019 |
06-04-2019 |
06-05-2019 |
06-06-2019 |
07-04-2019 |
07-05-2019 |
07-06-2019 |
08-04-2019 |
08-05-2019 |
08-06-2019 |
09-04-2019 |
09-05-2019 |
09-06-2019 |
10-04-2019 |
10-05-2019 |
10-06-2019 |
11-04-2019 |
11-05-2019 |
11-06-2019 |
12-04-2019 |
12-05-2019 |
12-06-2019 |
13-04-2019 |
13-05-2019 |
13-06-2019 |
14-04-2019 |
14-05-2019 |
14-06-2019 |
15-04-2019 |
15-05-2019 |
15-06-2019 |
16-04-2019 |
16-05-2019 |
16-06-2019 |
17-04-2019 |
17-05-2019 |
17-06-2019 |
18-04-2019 |
18-05-2019 |
18-06-2019 |
19-04-2019 |
19-05-2019 |
19-06-2019 |
20-04-2019 |
in this directly weeks hardcoded like 1 from 1st week 8 come 2nd 16 come third 24 come 4th
now for example today date is 20th June so 3rd week having 16th and after 17-20 days also user want upto 24
when 24 is coming they taken 4th week ..remove in middle 17-23 like script
let vDataFolder = 'C:\User 4\Groupwise inventory\';
// enumerate files
for each vFile in filelist('$(vDataFolder)*.xlsx')
// Get just the file name
let vFileName = mid(vFile, index(vFile, '\', -1) + 1);
// Enumerate sheets
NEW:
LOAD *,
'$(vFileName)' as [File Name],
FileBaseName('$(vFileName)') as filebasename
// '$(vSheetName)' as [Sheet Name]
FROM [$(vFile)]
(ooxml, embedded labels, table is Sheet1);
next
//NoConcatenate
load * ,
IF(WILDMATCH(filebasename,'01-04-20*'),'April1st',
IF(WILDMATCH(filebasename,'08-04-20*'),'April2nd',
IF(WILDMATCH(filebasename,'16-04-20*'),'April3rd',
IF(WILDMATCH(filebasename,'24-04-20*'),'April4th',
IF(WILDMATCH(filebasename,'01-05-20*'),'May1st',
IF(WILDMATCH(filebasename,'08-05-20*'),'May2nd',
IF(WILDMATCH(filebasename,'16-05-20*'),'May3rd',
IF(WILDMATCH(filebasename,'24-05-20*'),'May4th',
IF(WILDMATCH(filebasename,'01-06-20*'),'June1st',
IF(WILDMATCH(filebasename,'08-06-20*'),'June2nd',
IF(WILDMATCH(filebasename,'16-06-20*'),'June3rd',
IF(WILDMATCH(filebasename,'24-06-20*'),'June4th',
IF(WILDMATCH(filebasename,'01-07-20*'),'July1st',
IF(WILDMATCH(filebasename,'08-07-20*'),'July2nd',
IF(WILDMATCH(filebasename,'16-07-20*'),'July3rd',
IF(WILDMATCH(filebasename,'24-07-20*'),'July4th',
IF(WILDMATCH(filebasename,'01-08-20*'),'Augest1st',
IF(WILDMATCH(filebasename,'08-08-20*'),'Augest2nd',
IF(WILDMATCH(filebasename,'16-08-20*'),'Augest3rd',
IF(WILDMATCH(filebasename,'24-08-20*'),'Augest4th',
IF(WILDMATCH(filebasename,'01-09-20*'),'Sept1st',
IF(WILDMATCH(filebasename,'08-09-20*'),'Sept2nd',
IF(WILDMATCH(filebasename,'16-09-20*'),'Spet3rd',
IF(WILDMATCH(filebasename,'24-09-20*'),'Sept4th',
IF(WILDMATCH(filebasename,'01-10-20*'),'Oct1st',
IF(WILDMATCH(filebasename,'08-10-20*'),'Oct2nd',
IF(WILDMATCH(filebasename,'16-10-20*'),'Oct3rd',
IF(WILDMATCH(filebasename,'24-10-20*'),'Oct4th',
IF(WILDMATCH(filebasename,'01-11-20*'),'Nov1st',
IF(WILDMATCH(filebasename,'08-11-20*'),'Nov2nd',
IF(WILDMATCH(filebasename,'16-11-20*'),'Nov3rd',
IF(WILDMATCH(filebasename,'24-11-20*'),'Nov4th',
IF(WILDMATCH(filebasename,'01-12-20*'),'Dec1st',
IF(WILDMATCH(filebasename,'08-12-20*'),'Dec2nd',
IF(WILDMATCH(filebasename,'16-12-20*'),'Dec3rd',
IF(WILDMATCH(filebasename,'24-12-20*'),'Dec4th',
IF(WILDMATCH(filebasename,'01-01-20*'),'Jan1st',
IF(WILDMATCH(filebasename,'08-01-20*'),'Jan2nd',
IF(WILDMATCH(filebasename,'16-01-20*'),'Jan3rd',
IF(WILDMATCH(filebasename,'24-01-20*'),'Jan4th',
IF(WILDMATCH(filebasename,'01-02-20*'),'Feb1st',
IF(WILDMATCH(filebasename,'08-02-20*'),'Feb2nd',
IF(WILDMATCH(filebasename,'16-02-20*'),'Feb3rd',
IF(WILDMATCH(filebasename,'24-02-20*'),'Feb4th',
IF(WILDMATCH(filebasename,'01-03-20*'),'March1st',
IF(WILDMATCH(filebasename,'08-03-20*'),'March2nd',
IF(WILDMATCH(filebasename,'16-03-20*'),'March3rd',
IF(WILDMATCH(filebasename,'24-03-20*'),'March4th')))))))))))))))))))))))))))))))))))))))))))))))) as NewBase
Resident NEW;
drop table NEW;
i written this upto please help for extend