topic Re: Mark Files with hihgest Date in Connectivity & Data Prep

Mark Files with hihgest Date

MME2 — Sun, 30 Jan 2022 06:50:33 GMT

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 🙂

Re: Mark Files with hihgest Date

Vegar — Sun, 30 Jan 2022 07:16:49 GMT

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

Re: Mark Files with hihgest Date

MME2 — Sun, 30 Jan 2022 12:46:18 GMT

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

Re: Mark Files with hihgest Date

Vegar — Sun, 30 Jan 2022 21:01:25 GMT

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
;

Re: Mark Files with hihgest Date

MME2 — Mon, 31 Jan 2022 06:33:20 GMT

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.