topic Improve min date efficiency in load script in QlikView

Improve min date efficiency in load script

nsm1234567 — Thu, 10 Sep 2015 09:19:06 GMT

Hi There,

I'm hoping someone can assist me in improving a script I have running. The idea is to get the start time for each till, which is the minimum transaction time. The problem is that my source data contains many millions of rows, and the group by in TABLE2 takes a long time to complete. Are there any alternatives I can try? Any help would be greatly appreciated. The below is my current code

FOR each file in FileList('Myfilepath.qvd')

TABLE1

LOAD

Branch,

Till,

Date,

Time

FROM

$(file) (qvd);

TABLE2:

LEFT JOIN(TABLE1)

LOAD

Branch,

Till,

Date,

TIME(MIN([Time])) as [Time]

Resident TABLE1

Group by Till,

Date,

Branch;

Re: Improve min date efficiency in load script

petter — Thu, 10 Sep 2015 10:37:42 GMT

FOR each file in FileList('Myfilepath.qvd')

TABLE1:

LOAD

AutoNumber( Branch & '-' & Till & '-' & Date) AS %Key,

Branch,

Till,

Date,

Time

FROM

$(file) (qvd);

MIN_TABLE:

LOAD

AutoNumber( Branch & '-' & Till & '-' & Date) AS %Key,

Min(Time) AS MinTime

RESIDENT TABLE1

GROUP BY Till,Date,Branch;

So you effectively create yourself a Min Date dimension.

Re: Improve min date efficiency in load script

hic — Thu, 10 Sep 2015 11:30:55 GMT

I suspect that it is not the Group By that is the problem - instead the Join most likely is. So, I would try not joining.

FOR each file in FileList('Myfilepath.qvd')

TABLE1:

LOAD

Branch & '|' & Till & '|' & Date as Key,

Branch, Till, Date, Time

FROM $(file) (qvd);

TABLE2:

LOAD

Key,

TIME(MIN([Time])) as [Time]

Resident TABLE1

Group by Key;

HIC