topic Value for maximum time in QlikView

Value for maximum time

Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Value for maximum time

its_anandrjs — Mon, 31 Jul 2017 09:12:06 GMT

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

Re: Value for maximum time

rahulpawarb — Mon, 31 Jul 2017 09:16:01 GMT

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

Re: Value for maximum time

Kushal_Chawda — Mon, 31 Jul 2017 10:25:41 GMT

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;

Re: Value for maximum time

Tue, 01 Aug 2017 20:45:57 GMT

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;