topic Re: max in condition in QlikView

max in condition

Thu, 09 Oct 2014 12:14:28 GMT

i have table like this:

OrderSpecificationDim_Key	TypeCheekDim_Key	adSanadId
1391002317	20	26886
1391002317	3	29402
1391002318	20	26888
1391002319	20	26890
1391002319	23	29611
1391002320	3	51628
1391002320	23	29609
1391002320	20	26892

i need too show TypeCheekDim_Key for any OrderSpecificationDim_Key forthe max value of adSanadId

for example when OrderSpecificationDim_Key=1391002320 i should show 3 because it has the biggest value of adSanadId. i have wrote :

if ( (Aggr(max([Ad Sanad Id]),[Id Order])=[Ad Sanad Id]),[Type Check Dim Key],0 )

but it is not true it seem. any one can help me?

Re: max in condition

anbu1984 — Thu, 09 Oct 2014 12:25:51 GMT

=FirstSortedValue(TypeCheekDim_Key,-adSanadId)

Re: max in condition

Thu, 09 Oct 2014 12:37:32 GMT

hi anbu

it doesn't work

Re: max in condition

JonnyPoole — Thu, 09 Oct 2014 12:41:48 GMT

In this example, i'm using a chart expression to filter for just the records for the largest adSanaid within OrderSpecificationDim_Key

Re: Re: max in condition

anbu1984 — Thu, 09 Oct 2014 12:43:10 GMT

Can you post sample qvw. Check my qvw

Re: max in condition

Thu, 09 Oct 2014 12:45:52 GMT

hello jonathan

sorry i cant open it because of license limitations, would you please write the code here?

Re: max in condition

JonnyPoole — Thu, 09 Oct 2014 12:51:28 GMT

Add the OrderSpecificationDim_Key and adSanadId field as dimensions in a straight table and then add an expression with this formula :

if ( max( total <OrderSpecificationDim_Key> adSanadId) =adSanadId , TypeCheekDim_Key)

and the load script was nothing special:

LOAD OrderSpecificationDim_Key,

TypeCheekDim_Key,

adSanadId

FROM

[http://community.qlik.com/thread/137133]

(html, codepage is 1252, embedded labels, table is @1);

Re: max in condition

antoniotiman — Thu, 09 Oct 2014 12:58:01 GMT

Hi Mana,

see attachment.

Regards,

Antonio

Re: max in condition

Thu, 09 Oct 2014 13:04:23 GMT

hello Antonio

sorry i cant open it because of license limitations, would you please write the code here?

Re: Re: max in condition

antoniotiman — Thu, 09 Oct 2014 13:09:46 GMT

I have used calculated dimension :

=Aggr(If(Max(TOTAL adSanadId)=adSanadId,OrderSpecificationDim_Key),OrderSpecificationDim_Key,adSanadId,TypeCheekDim_Key)

Re: max in condition

Thu, 09 Oct 2014 13:16:13 GMT

hello Antonio

well,it doesn't work 😞

Re: max in condition

antoniotiman — Thu, 09 Oct 2014 13:21:09 GMT

Re: max in condition

anbu1984 — Thu, 09 Oct 2014 13:26:10 GMT

Dimension: OrderSpecificationDim_Key

Expression: FirstSortedValue(TypeCheekDim_Key,-adSanadId)

Re: Re: max in condition

Thu, 09 Oct 2014 13:49:40 GMT

it doesnt work in my qlikview. i have attached it.

id order=orderspecification_key

Re: Re: max in condition

Thu, 09 Oct 2014 13:53:24 GMT

hello

it doesnt work in my qlikview file. i have attached it.

id order=orderspecification_key

Re: Re: max in condition

Thu, 09 Oct 2014 13:58:10 GMT

if you could correct it would you pls write it here?

Re: Re: max in condition

Thu, 09 Oct 2014 13:58:19 GMT

if you could correct it would you pls write it here?

Re: max in condition

anbu1984 — Thu, 09 Oct 2014 13:59:05 GMT

Can you give [id order] where it is not working? What is expected value?

Re: Re: max in condition

Thu, 09 Oct 2014 14:00:50 GMT

id order is the field. orderspecification_key is just a name.

Re: max in condition

anbu1984 — Thu, 09 Oct 2014 14:05:11 GMT

I mean can you give example from your QVW, for which id order you are not getting correct result for TypeCheekDim_Key