Comparing excel files and only loading the latest possible (Incremental Load Combination)

[mrichman]

Hi Community,

I have an excel file w/ columns: name, date, and time. The excel files gets extracted multiple times a day, and we want to only take the latest file of that day into the (Incremental) Load. Do you guys have any idea on how to approach this the best way possible?

In words:

if the name and date columns are the same but has a different time, then load the only the one with max time.

Current case after (incremental) load:

Name             Date                  Time

Richman         13-05-2018        10:00

Richman         14-05-2018       10:00

Richman         14-05-2018       10:00

Richman          14-05-2018       10:05

Richman          14-05-2018       10:05

Richman          15-06-2018        10:00

Richman          15-06-2018          10:00

Ideal solution:

Richman         13-05-2018        10:00

Richman          14-05-2018       10:05

Richman          14-05-2018       10:05

Richman          15-06-2018        10:00

Richman          15-06-2018          10:00

Thanks in advance!

[sunny_talwar]

May be do a right join after the load to keep the max time

Right Join (...)

LOAD Name,

     Date,

     Max(Time) as Time

Resident...

Group By Name, Date;

[mrichman]

Hi Sunny,

Thanks for your prompt reply. I'm quite new to qlikview but how can I apply it to the underlying script (script is just for example).Thanks in advance.

Table1:

LOAD Name,

Date,

Time

FROM

[Source]

(ooxml, embedded labels, table is [Table], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 2)),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 999), Select(1, 0))),

Transpose(),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 24), Select(1, 0)))

))

Where Name<>'';

Join (Table1)

LOAD ProductName,

ProductCategory,

Inventory

FROM

[Source]

(ooxml, embedded labels, table is

, filters(

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

[sunny_talwar]

Something like this

Table1:

LOAD Name,

     Date,

     Time

FROM [Source]

(ooxml, embedded labels, table is [Table], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 2)),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 999), Select(1, 0))),

Transpose(),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 24), Select(1, 0)))

))

Where Name<>'';

Right Join (Table1)

LOAD Name,

     Date,

     Max(Time) as Time

Resident Table1

Group By Name, Date;

I am not sure what this is doing?

Join (Table1)

LOAD ProductName,

ProductCategory,

Inventory

FROM

[Source]

(ooxml, embedded labels, table is

, filters(

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

[mrichman]

I have added the underlying script but it is not working   the load script is still reading the old and new file.

Current Result:

Name             Date                  Time

Richman         13-05-2018        10:00

Richman         14-05-2018       10:00

Richman          14-05-2018       10:05

Richman          15-06-2018          10:00

Your question: "I am not sure what this is doing?"

I have this part of the script because both the table need to be joined. The upper part of the script is where the name, date, and time is located (transpose). Then I read the same excel again but getting the the additional information (rows).  See underlying illustration:

Excel format:

Name Richman

Date 13-05-2018

Time 10:00

ProductName            ProductCategory      Inventory

TV                             Brown                       1

Washingmachine      White                        3

All tips are welcome!

******************************************************************************************************************************

Table1:

LOAD Name,

    Date,

    Time

FROM [Source]

(ooxml, embedded labels, table is [Table], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 2)),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 999), Select(1, 0))),

Transpose(),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Top, 24), Select(1, 0)))

))

Where Name<>'';

Right Join (Table1)

LOAD Name,

    Date,

    Max(Time) as Time

Resident Table1

Group By Name, Date;

I am not sure what this is doing?

Join (Table1)

LOAD ProductName,

ProductCategory,

Inventory

FROM

[Source]

(ooxml, embedded labels, table is

, filters(

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));