Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Thanks in advance for your support. Allow me to share my challenge:
I am want to flag a filename which has the highest date in the month.
I am storing reports every week with the follwing naming convention:
Report1_YYYYMMDD.xlsx
For certain reporting purposes I need to report weekly (which is easy based on the import structure).
I need to build no however a monthly report which gives me the current month, but also the value of the last month.
What I can do is building an average base don the files (as the values can change week over week) that's why I always want to take the value from the file of the latest week in that month.
My Idea was to flag the file with the highest week on the month and then can use set analysis in the chart.
Here is what I would like to achieve:
Report | Flag
-------------------------------------------------
Report1_20220101 | No
Report1_20220107 | Yes
Report1_20220202 | No
Report1_20220215 | No
Report1_20220220 | Yes
Does anybody have an idea how to resolve this?
Many thanks for your support 🙂
With that approach you will get a duplicate row for all rows in your xlsx files. An better approach would be just to look at the filenames and not load the xlsx it self. (I also notice that your month calculation will not return any values)
Consider this:
For each _file in filelist('RAWDATA\Report1_*.xlsx')
FilenameList:
Load '$(_file)' as _filename
AutoGenerate 1;
next
MAP_Flag:
Mapping LOAD
Maxstring(_filename),
'Yes'
Resident FilenameList
Group By
monthname(Date#(left(right(_filename,13),8),'YYYYMMDD')) //Unique per year-month
;
FileName:
NoConcatenate LOAD
_filename as FileName,
Date#(left(right(_filename,13),8),'YYYYMMDD') as Date,
ApplyMap('MAP_Flag',_filename,'No') as Flag //Will return Yes if exists in map else it will be No
Resident FilenameList;
drop table FilenameList
;
You can solve this by creating a mapping table like below.
[Map_maxreport]:
Mapping Load
maxstring(Report) as Report,
'Yes' as _isMonthlyMaxReport
Inline [
Report
Report1_20220101
Report1_20220107
Report1_20220202
Report1_20220215
Report1_20220220 ]
Group By left(Report,14);
Then use it with 'No' as default value when loading your Report names.
Load Report,
applymap('Map_maxreport',Report,'No') as Flag
Inline [
Report
Report1_20220101
Report1_20220107
Report1_20220202
Report1_20220215
Report1_20220220 ];
Thanks for your swift reply, however I have to admit having challenges putting this into my script.
I am loading the files from a local folder:
FileName:
LOAD
FileName() as FileName,
(Date(Date#(left(right(FileName(),13),8),'YYYYMMDD'))) as Date,
(Date(Date#(left(right(FileName(),9),2),'YYYYMMDD'))) as Month
FROM
RAWDATA\Report1_*.xlsx
(ooxml, embedded labels, header is 5 lines, table is );
Could you kindy help me out with the mapping logic?
Appreciate your knowledge.
Greetings
Michael
With that approach you will get a duplicate row for all rows in your xlsx files. An better approach would be just to look at the filenames and not load the xlsx it self. (I also notice that your month calculation will not return any values)
Consider this:
For each _file in filelist('RAWDATA\Report1_*.xlsx')
FilenameList:
Load '$(_file)' as _filename
AutoGenerate 1;
next
MAP_Flag:
Mapping LOAD
Maxstring(_filename),
'Yes'
Resident FilenameList
Group By
monthname(Date#(left(right(_filename,13),8),'YYYYMMDD')) //Unique per year-month
;
FileName:
NoConcatenate LOAD
_filename as FileName,
Date#(left(right(_filename,13),8),'YYYYMMDD') as Date,
ApplyMap('MAP_Flag',_filename,'No') as Flag //Will return Yes if exists in map else it will be No
Resident FilenameList;
drop table FilenameList
;
Hi Vegar,
many thanks again for your prompt and great support.
I will put it into my script and see if it works there.
In a dummy file it was working already as expected.
Again many thanks and appreciate the great and nice support!
Have a great week.