topic Re: How to use column(1) value in expression in QlikView

How to use column(1) value in expression

Sat, 18 Jul 2015 11:12:32 GMT

Dear Experts ,

In my report i want to count no of shipment and display origin in expression . Please look in to my qvw and attached image .

I want to count no of shipment for the last 6 months before the last job date , i,e my last job date is 21-Mar-2014 .

totally there is 5 shipments from last job to last job - 180 (41539 to 41719) . also i want to show my origin value for the max job id 41719 .

Please help me .

Re: How to use column(1) value in expression

sunny_talwar — Sat, 18 Jul 2015 11:28:23 GMT

I see only one shippment which falls under the Max(Job_ID) and Max(Job_ID)-180

If what I am saying is right, then this expression is working:

=Count({<Job_ID = {"$(='>=' & (Max(Job_ID)-180))"}>}NO_OF_SHIPMENT)

For origin, try this expression:

FirstSortedValue(Origin, Job_ID)

Output Table:

HTH

Best,

Sunny

Re: How to use column(1) value in expression

swuehl — Sat, 18 Jul 2015 12:11:09 GMT

I believe the HELP is misleading that you are not just referencing the value of first column by using column(1), but you are referencing the expression. And since your expression is an aggregation function, you need advanced aggregation if you want to embed it in another aggregation.

maybe like attached.

Re: How to use column(1) value in expression

Mon, 20 Jul 2015 06:16:20 GMT

Thanks for your reply ,

Please select date in calendar (30-jun-15), totally there is 5 shipment between job id 41719 to 41539 . i tried with your solution , but it always showing single shipment for other customer also .

Also i want to show max job id origin value . Firstsorted value is taking min job id value .

kindly help me to solve .

Re: How to use column(1) value in expression

Mon, 20 Jul 2015 07:07:24 GMT

Dear Swuehl ,

Thanks a lot its working perfectly , but if i change date range , its taking more time to load , i think its happening because of huge data .

Just one small thing , if my last job date is null , then i want to hide customer row. i have selected suppress zero values in presentation for all expressions , but still service steel customer is appearing in report .

kindly look my image file and suggest .

Re: How to use column(1) value in expression

swuehl — Mon, 20 Jul 2015 07:34:07 GMT

Can you limit your data to your current selection and upload the QVW?

Re: How to use column(1) value in expression

Mon, 20 Jul 2015 09:38:50 GMT

Dear Swuehl ,

i have attached qvw with sample customer . if my last job date is null , then i want to hide customer row.

Kindly suggest .

Re: How to use column(1) value in expression

jagan — Mon, 20 Jul 2015 09:47:41 GMT

Hi Sriram,

PFA file for solution.

Regards,

Jagan.

Re: How to use column(1) value in expression

swuehl — Mon, 20 Jul 2015 09:53:31 GMT

Attached should work.

Re: How to use column(1) value in expression

Wed, 22 Jul 2015 12:14:16 GMT

Dear Swuehl ,

If my count of max (job_id) is > 1 and origin or destination have different value , then its not showing in out put .

For example customer Agritrade , max job is 41502 where destination have 2 different value , i want to display yangon in destination which displaying first in table box .

For customer 3m india limited , both origin and destination value is null ,

Kindly suggest . Thanks in advance for your continued support .

Re: How to use column(1) value in expression

swuehl — Wed, 22 Jul 2015 21:47:22 GMT

What is determining the priority, GP_LOCAL?

Try for destination:

if(column(1),

Concat(

aggr(

if( max(total<Customer> if(Len(if(JOB_DATE>=vToDate-vRange and JOB_DATE<=vToDate,JOB_REF))=0 and Len(if(JOB_DATE<=vToDate-vRange,JOB_REF))>0 and Len(if(JOB_DATE>=vToDate,JOB_REF))=0,JOB_DATE)) = JOB_DATE

, Destination), Customer, JOB_DATE, Destination),', ' ,-GP_LOCAL)

,0)