topic Re: conditional result for the expression in nested if in QlikView

conditional result for the expression in nested if

Thu, 27 Oct 2016 11:04:51 GMT

Hello all !

In one of my table I have a column to display the price which comes in variable 'M' from a QVD. My dimension is the item name and the corresponding price (Here 'M') should be shown. I have to show the latest price if more than one price is available in the QVD. The column to identify the latest price is 'MonthYear'. Please help me in modifying the expression so that it will display the latest price.

So here we have to modify the expression part of first if , that is Max (M) . It should be Latest(M) according to MonthYear column.

If(Count(DISTINCT(M))>1,Max(M),if(Count(DISTINCT(M))=0,0,if(Count(DISTINCT(M))=1,M)))

-J

Re: conditional result for the expression in nested if

vinieme12 — Thu, 27 Oct 2016 11:08:23 GMT

try

firstsortedvalue(M,-MonthYear)

Re: conditional result for the expression in nested if

sunny_talwar — Thu, 27 Oct 2016 11:08:32 GMT

May be try this:

FirstSortedValue(M, -MonthYear)

Re: conditional result for the expression in nested if

Thu, 27 Oct 2016 11:29:19 GMT

YaaY ! It works ! Thanks a ton to both of you !

-J

Re: conditional result for the expression in nested if

Fri, 28 Oct 2016 06:32:34 GMT

Sorry guys ! After full round of testing I found the expression firstsortedvalue(M,-MonthYear) was not working properly. Please help me out. stalwar1‌ vinieme12‌

I have pasted the snap of my table with columns M with firstsortedvalue(M,-MonthYear) expression , M with Max(M) expression and Count(M) for your reference.

Thanks,

James

Re: conditional result for the expression in nested if

vinieme12 — Fri, 28 Oct 2016 06:39:29 GMT

Can you post a sample app?

Re: conditional result for the expression in nested if

Fri, 28 Oct 2016 07:07:28 GMT

Here you go . For the selected material there are more than one 'M price' . We have to display latest m price out of that. Check the expressions of all the objects in the dashboard.

-J

Re: conditional result for the expression in nested if

sunny_talwar — Fri, 28 Oct 2016 09:41:36 GMT

Where is this chart in the sample? Also what is the dimension here in the image you posted?

Re: conditional result for the expression in nested if

Mon, 31 Oct 2016 05:51:35 GMT

Hello !

Please find the file attached. Item is the dimension here.

-J

Re: conditional result for the expression in nested if

Tue, 01 Nov 2016 12:45:39 GMT

Hi stalwar1‌ ,

Is it possible to display the records as i expect ? Because I tried a lot but no luck at all

Regards,

Re: conditional result for the expression in nested if

vinieme12 — Tue, 01 Nov 2016 14:40:54 GMT

Your MatPlantMMYY field should be number or a datevalue, so create a new YearMonth field using YearNo and MonthNo fields

YearNo&NUM(MonthNo,'##') as YearMonth

in the expression use

= firstsortedvalue(MAPeur,-YearMonth)

Re: conditional result for the expression in nested if

vinieme12 — Tue, 01 Nov 2016 14:50:47 GMT

or Try this; but you need to get rid of the null values in those fields, for the year assign value like 1000 and month 0!

FirstSortedValue(MAPeur,-(YearNo&MonthNo))

Re: conditional result for the expression in nested if

Wed, 02 Nov 2016 06:35:31 GMT

Thanks for your reply !

But still I am getting null ( - ) for some of the items whose M count is > 1. Also I am not able to understand your 1000 and 0 setting

But most of the M values are displayed now. PFB screenshot.

Regards,

Re: conditional result for the expression in nested if

tresB — Wed, 02 Nov 2016 06:54:26 GMT

Hi Bond,

So your weapons can't help you here.

I your requirement clear enough to me. Could you please explain your expected output with logic, against your sample file?

Re: conditional result for the expression in nested if

Wed, 02 Nov 2016 07:00:38 GMT

For the rows which are null ( - ) in the M first sorted are null because there are more than one M values (see M count for those null rows). So we have to tell QlikView which M value should display. The condition is latest M value and it cn be implemented by using FirstsortedValue with weightage as YearNo&MonthNo which will identify the latest M value . So now it identified the latest M value for most of the items , but still some of the items are null ( - ) (as highlighted yellow in my last screenshot). We need to display the M values for those too now.

-J

Re: conditional result for the expression in nested if

Anonymous — Wed, 02 Nov 2016 07:05:41 GMT

Try below one

Aggr(FirstSortedValue(MAPeur,-(YearNo&MonthNo)),MAPeur)

Re: conditional result for the expression in nested if

Wed, 02 Nov 2016 07:14:50 GMT

No luck man

Re: conditional result for the expression in nested if

tresB — Wed, 02 Nov 2016 07:16:42 GMT

Second parameter of firstsortedvalue() has to return a numeric value. But in your expression FirstSortedValue( MAPeur, -MatPlantMMMYY), MatPlantMMMYY is a string. So it doesn't work.

Re: conditional result for the expression in nested if

Wed, 02 Nov 2016 07:20:48 GMT

Yes accepted. Now i changed it to YearNo&MonthNo. That is why I am getting values for some of the rows (see the row above the highlighted row in my latest screenshot). I am still worried about rows like the highlighted ones. why the sort weight '-YearNo&MonthNo' is not working for those ?

-J

Re: conditional result for the expression in nested if

tresB — Wed, 02 Nov 2016 07:22:14 GMT

One more issue noticed - most of the cases there is no MAPeur value on latest date. Ex. KNED-> 4J6K1029P001

latest date is Nov2016, and there is no MAPeur. Hence, firstsortedvalue would not get anything to return even if you provide the date field as the second parameter.

Had there been data for latest date, you could probably even use something like:

FirstSortedValue( MAPeur, -Date#(Right(MatPlantMMMYY, 7),'MMMYY'))