Max function not working in the Where condition

[swathidumpala]

Hi Team ,

When I ran  the script with  ( where MKY='201907' ) condition it loaded well.

As part of automating the script when  I replaced the same 

with  num#(MKY) = max(num#(MKY)) I got an error while loading.

I am unsure why it is happening.

Requesting help!

Thanks,

Swati

[tresesco]

Max() is an aggregation function and that can't be used in where clause here

[marcus_sommer]

An alternatively to the max-condition in the where-clause would be to transfer it to an inner join, like:

t: load ID, MKY from Source;
       inner join(t)
    load ID, max(MKY) as MKY resident t group by ID;

- Marcus

[kaanerisen · Accepted solution]

Alternatively, you can use a reference table.

Ref_Table:
Load ID,max(MKY) as MKY from Source Group By ID;

Fact_Table:
Load * from Source where Exists(MKY); 

Drop Table Ref_Table;

[swathidumpala]

I just want the maximum value of a filed with out any group by like the one below from teradata.

sel max(MKY)  as MKY from  Source;

Can we try as below:

Ref_Table:
Load max(MKY) as MKY from Source ;

[Channa]

Load date using Preceding load 

like Having clause in your SQL

[swathidumpala]

Thanks Channa,

Can you please give some example on it as I am very new to Qlik Coding.

Thanks,

Swati

[swathidumpala]

Thanks Marcus,

But I need the maximum of  MKY  on filed level not on any group.

[Channa]

give me sample data file

or

this below is other why of using having clause in chart level

it will bring climID where amount >1000

Count(Distinct {<ClaimID={"=Sum(BilledAmount)>1000"}>} ClaimID)

[swathidumpala]

Thanks Marcus,

The data is :

Id  MKY

1  08

1 07

1 06

2 07

2 06

3 08

3 07

3 06

I need the MKY to be only 08.

if we take group by for 2 we also get 07.

The result should be 

Id  MKY

1 08

3 08