Value for maximum time

[#1]

Hi all,

I have one table like this into 10 excel files:

TIME    VALUE    DATE

10:50    154    29/07/2017

09:20    986    29/07/2017

08:13    1456    29/07/2017

08:05    3423    29/07/2017

11:25    342    29/07/2017

i need to make one table that for every file has the value based on max time. Like the following

FILE_NAME  VALUE  DATE

DATA1      342    29/07/2017

DATA2 ...

I want to do this in my load script. I tried the following script, it works but i'm sure it's not the best practice. i got 10 synthetic keys. How it can be avoided?

Thank you for your help,

Andrea

=======================

FILE_NAME:

NoConcatenate

LOAD distinct FILE_NAME

FROM

(ooxml, embedded labels);

for vRow = 1 to NoOfRows('FILE_NAME')

Let filename = Peek('FILE_NAME',vRow-1,'FILE_NAME');

TABLE_DATA:

LOAD '$(filename)' as FILE_NAME,

     TIME,

     VALUE,

     DATE

FROM

(ooxml, embedded labels);

MAX:

load '$(filename)' as FILE_NAME,

  DATE,

  Max(TIME) as MAXTIME

Resident TABLE_DATA

Group By FILE_NAME, DATE; 

Left join

load

'$(filename)' as FILE_NAME,

TIME as MAXTIME,

VALUE

Resident TABLE_DATA;

drop table TABLE_DATA;

next vRow;

[its_anandrjs]

Hi,

You can try this ways

Final_Table:

Load

TIME,

VALUE,

DATE,

Filebasename() as Filename

From Location;

Concatenate(Final_Table)

Load

TIME,

VALUE,

DATE

Filebasename() as Filename

From Location;

MaxTab:

Left Join(Final_Table)
Load

Filename,

Max(TIME) as MAXTIME

Resident Final_Table;

Note:- You can load it later this tables.

Regards,

Anand

[rahulpawarb]

May be this:

Data:
LOAD
     FileBaseName() AS FileName,
     TIME,
     VALUE,
     DATE
FROM *.xlsx
(ooxml, embedded labels, table is Sheet1);
INNER JOIN
LOAD FileName,
            Max(VALUE) AS VALUE
Resident Data
Group By FileName;

Consideration: All .xlsx files are available at same location.

Regards!

Rahul Pawar

[Kushal_Chawda]

FILE_DATA:

NoConcatenate

LOAD distinct FILE_NAME as FILE_NAME1

FROM

(ooxml, embedded labels);

for i=1 to FieldValueCount('FILE_NAME');

let vFileName = FieldValue('FILE_NAME',$(i));

Concatenate(FILE_DATA)

LOAD '$(vFileName)' as FILE_NAME,

     floor(TIME) as TIME,

     VALUE,

     DATE

FROM

(ooxml, embedded labels);

NEXT i

DROP Field FILE_NAME1;

Inner Join(FILE_DATA)

LOAD FILE_NAME,

     max(TIME) as TIME

Resident FILE_DATA

Group by FILE_NAME;

[#5]

thank you everybody. I've kept the join outside the for..next and now works fine

FILE_NAME:

NoConcatenate

LOAD distinct FILE_NAME

FROM

(ooxml, embedded labels);

for vRow = 1 to NoOfRows('FILE_NAME')

Let filename = Peek('FILE_NAME',vRow-1,'FILE_NAME');

TABLE_DATA:

LOAD '$(filename)' as FILE_NAME,

     TIME,

     VALUE,

     DATE

FROM

(ooxml, embedded labels);

next vRow

Left Join (TABLE_DATA)

Load

FILE_NAME,

DATE,

max(TIME) as MAXTIME

Resident TABLE_DATA Group by DATE, FILE_NAME;

Final:

Noconcatenate

Load * Resident TABLE_DATA Where TIME = MAXTIME;

Drop Table TABLE_DATA;