topic Re: How to display only 1 record instead all (expressions) in QlikView

How to display only 1 record instead all (expressions)

Thu, 03 Jan 2013 04:30:06 GMT

Dear all,

I find it difficult to get only the 1 record base on my period selection.

In following casse, the period is 30 of november 2012, i wish to get result only the 16/11/2012 instead of 01/04/2011.

In other words, the top pccupncy date to the period i selected.

Billion thanks to you, if you can help on this.

Re: How to display only 1 record instead all (expressions)

khadeer — Thu, 03 Jan 2013 04:59:28 GMT

firstsortedvalue(Total Building,Occupncy)

hope it will help u.

Regards,

Khadeer

Re: How to display only 1 record instead all (expressions)

Thu, 03 Jan 2013 05:14:26 GMT

firstsortedvalue(Total Building,Occupncy) doesn't work well if i have multiple records which consists of many suitid there.

I hope to select only the closest to my period selection in this case nov 2012. in my data might consists other suitid too. so all of the suitid records will use thier own one with closest Occuncy date as output.

the logic behind for sql is something like

select top 1 buildingNLA

where occupncy <= period

order occupncy desc

Re: How to display only 1 record instead all (expressions)

Thu, 03 Jan 2013 06:43:08 GMT

Hi in ur expression add this condition too.

Sum(if(isnull(somthing) and OCCPNCY=Max(OCCPNCY),'True','False'))

~Kabilan K

Re: How to display only 1 record instead all (expressions)

Thu, 03 Jan 2013 06:55:17 GMT

Hi, Kabilan & QV learner

Thanks for your reply.. The concept for the expressions is basically like following sql statement,

select top 1 buildingNLA

where occupncy <= period

order occupncy desc

Hi, Kabilan

The script you shared is not working and error. Please correct me if i were wrong.

sum(if(IsNull(OCCUPNCY)=0 AND Max(OCCUPNCY)='True','False',BuildingNLA

))

Re: How to display only 1 record instead all (expressions)

Thu, 03 Jan 2013 07:30:31 GMT

I modify a bit "QV Learner" script, it seen work and i use total sum of the row to display the Total.

Aggr

(FirstSortedValue(BuildingNLA,-[OCCUPNCY]),SUITID2)

Now, the issue left the OCCUPNCY date will change base on what i have select in my period value.