topic Re: Comparing excel files and only loading the latest possible (Incremental Load Combination) in QlikView

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

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

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:05

Richman 15-06-2018 10:00

Ideal solution:

Richman 13-05-2018 10:00

Richman 14-05-2018 10:05

Richman 15-06-2018 10:00

Thanks in advance!

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

sunny_talwar — Mon, 14 May 2018 19:13:24 GMT

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;

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

mrichman — Mon, 14 May 2018 19:31:08 GMT

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))

));

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

sunny_talwar — Mon, 14 May 2018 19:33:07 GMT

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))

));

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

mrichman — Mon, 14 May 2018 19:51:56 GMT

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))

));