topic Re: Re: Max Subquery in QlikView

Max Subquery

Mon, 21 Oct 2013 17:12:50 GMT

Aggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LastRunId:

load max(run_id) resident Aggregates;

AggregatesForLastRun:

load * Resident Aggregates

where run_id >= LastRunId;

Any way to make this work?

Re: Max Subquery

ronaldocarrijo — Mon, 21 Oct 2013 17:21:35 GMT

Thus have the same effect, more simply:

Aggregates:

LOAD Max(RUN_ID) as RUN_ID,

EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

GROUP BY EXECUTION_DATE;

Re: Max Subquery

Anonymous — Mon, 21 Oct 2013 17:25:05 GMT

I assume there can't be run_id > LastRunId, can be only run_id = LastRunId
Try this:

Aggregates:
LOAD
RUN_ID,
EXECUTION_DATE
FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LastRunId:
inner keep (Aggregates)
load max(RUN_ID) as RUN_ID
resident Aggregates;

DROP TABLE LastRunId

Re: Max Subquery

Tue, 22 Oct 2013 07:35:28 GMT

Your solution doesn't work....

Re: Max Subquery

Tue, 22 Oct 2013 07:36:08 GMT

I want a solution like Michael that uses the table already in memory instead of reloading again from the source and wasting time.

Re: Max Subquery

Tue, 22 Oct 2013 08:09:52 GMT

My last code can't find column [AggregatesForLastRun.MAX_RUN_ID]...

Raw:

LOAD RUN_ID,

EXECUTION_DATE,

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

qualify *;

Aggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

AggregatesForLastRun:

load max([Aggregates.RUN_ID]) as MAX_RUN_ID Resident Aggregates

Group BY [Aggregates.RUN_ID];

LastRunId:

load * Resident Aggregates

where [Aggregates.RUN_ID] >= [AggregatesForLastRun.MAX_RUN_ID];

unqualify *;

Re: Max Subquery

Tue, 22 Oct 2013 08:18:50 GMT

In SQL this is just so easy...Why do they have to complicate?

select *

from Aggregates A

where A.RUN_ID = select max(run_id) from Aggregates A2 group by A2.RUN_ID

Re: Re: Max Subquery

ronaldocarrijo — Tue, 22 Oct 2013 10:25:41 GMT

Works yes my friend, attached an example I did.

Re: Max Subquery

Tue, 22 Oct 2013 10:52:17 GMT

This also doesn't work:

Aggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\FOO.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

inner keep (Aggregates)

load max(RUN_ID) as MAX_RUN_ID

resident Aggregates;

LastRunId:

load * Resident Aggregates

where [RUN_ID] = MAX_RUN_ID;

Re: Max Subquery

Tue, 22 Oct 2013 10:54:54 GMT

But you're loading the same table twice.

If you have 1 million rows that;s not feasible in terms of performance.

Re: Max Subquery

Anonymous — Tue, 22 Oct 2013 12:15:38 GMT

Now it's time to explain in words what you want to achieve...

The condition:
where A.RUN_ID = select max(run_id) from Aggregates A2 group by A2.RUN_ID

returns every single value of run_id because you group it by itself. What did I get wrong?

Re: Max Subquery

Tue, 22 Oct 2013 12:19:44 GMT

so I've done the SQL query.

Can you translate it to qlikview, because I'm a beginner in QlikView?

Thanks,

Miguel

Re: Max Subquery

Tue, 22 Oct 2013 12:23:13 GMT

Here's my latest Script and still not working:

qualify *;

Raw:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\Raw.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Aggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

inner keep (Aggregates)

load max([Aggregates.RUN_ID]) as MAX_RUN_ID

resident Aggregates

group by [Aggregates.RUN_ID];

let mypeek = peek('[MaxRunId.MAX_RUN_ID]',0,'MaxRunId');

MaxAggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\MIB1-FOO-aggregates.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

where RUN_ID = $(mypeek);

unqualify *;

Re: Max Subquery

Anonymous — Tue, 22 Oct 2013 12:40:48 GMT

Let me ask you the last time before I give up

What is the difference in results between:

1) your SQL Query

select *

from Aggregates A

where A.RUN_ID = select max(run_id) from Aggregates A2 group by A2.RUN_ID

I have to add that your query doesn't work for me. This works:

select *

from Aggregates A

where A.RUN_ID in (select max(run_id) from Aggregates A2 group by A2.RUN_ID)

2) and this SQL

select *

from Aggregates A

The "where" condition as you have it now doesn't do anything.

3) and your code (first part):

Aggregates:

LOAD

RUN_ID,

EXECUTION_DATE

FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Re: Max Subquery

ronaldocarrijo — Tue, 22 Oct 2013 12:49:30 GMT

So I'm carrying twice to show it to u the original data and the data with Max, the more u will only use the second load

Re: Max Subquery

Tue, 22 Oct 2013 15:35:10 GMT

Problem solved!

Aggregates:

LOAD RUN_ID,

EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

load max([RUN_ID]) as MAX_RUN_ID

resident Aggregates;

let mypeek = peek('MAX_RUN_ID',0,'MaxRunId');

LastRunAggregates:

LOAD RUN_ID,

EXECUTION_DATE

resident Aggregates

where RUN_ID = $(mypeek);

Re: Max Subquery

Tue, 22 Oct 2013 15:37:53 GMT

So maybe you should quit, if you don't understand that:

RUN_ID VALUE

1 a

1 b

2 c

2 d

So my final comment marked as an answer just retrieves the rows where RUN_ID = 2, and if there a ROW_ID called 12, and 12 is the biggest RUN_ID, my script only return rows with 12 as RUN_ID.

Re: Max Subquery

Anonymous — Tue, 22 Oct 2013 16:11:59 GMT

"So maybe you should quit..." - thank you for this suggestion, I'll certainly consider it

Now, look again at my very first reply. To make it easier for you, I'm repeating it here:

Aggregates:
LOAD
RUN_ID,
EXECUTION_DATE
FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) ;

LastRunId:
inner keep (Aggregates)
load max(RUN_ID) as RUN_ID
resident Aggregates;

DROP TABLE LastRunId;

Look carefully - it works the same way as your solution, only a little faster. It even looks similar... Logically you're doing exactly the same, except you're using variable and additional "last run", while I'm using "inner keep".

I try to learn from everyone. You'll be fine if you do it too.

Re: Max Subquery

Anonymous — Tue, 22 Oct 2013 16:14:00 GMT

I can make it even a little shorter by using inner join:

Aggregates:
LOAD
RUN_ID,
EXECUTION_DATE
FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) ;

inner join (Aggregates)
load max(RUN_ID) as RUN_ID
resident Aggregates;

Re: Max Subquery

Wed, 23 Oct 2013 09:39:13 GMT

The problem is that your initial solution didn't work. Also your solution isn't faster because I need both tables.

Since my problem is solved now I'll not test your new proposed solutions.

Of course I like to learn with others but you're being a little arrogant here.