topic Re: Issue with firstsortedvalue in QlikView

Issue with firstsortedvalue

Tue, 30 Sep 2014 09:00:51 GMT

Hi Friends,

I'm struck with some issue related to firstsortedvalue.

I wrote a statement in my script like

firstsortedvalue(ID,-Time) as UserID----- This statement will return the id of the user based on the latest time.

firstsortedvalue(ID,Time) as UserID--- This statement will return the id of the user based on the earliest time.

But what I want is all the users who has done some particular task based on my where clause.

so I also tried just

ID as UserID--- But it is giving me all the data, wrt other users also.

Can you help me in this.

Thanks,

Sindhu

Re: Issue with firstsortedvalue

anbu1984 — Tue, 30 Sep 2014 09:16:11 GMT

What is your where clause?

Re: Issue with firstsortedvalue

Anonymous — Tue, 30 Sep 2014 09:18:47 GMT

Can you use the WHERE clause in the LOAD and SELECT process?

LOAD

...,

ID as UserID;

SQL SELECT

...,

FROM ..

WHERE clause;

If not, then you can try to use if() in the "Enable Conditional" in the Dimension tab to filter the ID you want.

Re: Issue with firstsortedvalue

Tue, 30 Sep 2014 09:19:24 GMT

My where clause is like from_task='Start' to_task='Build'

Re: Issue with firstsortedvalue

Tue, 30 Sep 2014 09:22:33 GMT

Hi Zhihong,

There are 100's of users, i cannot filter based on one user.
I tried in the expression with distinct keyword, but its not working.

Re: Issue with firstsortedvalue

anbu1984 — Tue, 30 Sep 2014 09:22:59 GMT

So you want the users who has both from_task='Start' and to_task='Build'?

Can you post your script along with your sample data.

Re: Issue with firstsortedvalue

Tue, 30 Sep 2014 09:26:53 GMT

Hi Anbu,

Left JOin(MasterTable)

Load

Bundle,

ID as UserID

From table.qvd

where from_task='Start'

and to_task='Build';

Re: Issue with firstsortedvalue

anbu1984 — Tue, 30 Sep 2014 09:38:17 GMT

What is join key to your master table? Above where clause won't affect results of master table since left join is performed

Re: Issue with firstsortedvalue

Tue, 30 Sep 2014 09:41:00 GMT

Bundle is the join key to the master table which is unique.

Yes it will not affect.

Re: Issue with firstsortedvalue

sasikanth — Tue, 30 Sep 2014 09:43:58 GMT

Try the same in Resident load , that may work

Re: Issue with firstsortedvalue

Anonymous — Tue, 30 Sep 2014 09:47:18 GMT

Hi, SIndhu,

I think it is possible that Where clause is ignored with LOAD directly, see same problem here .

You can try first load the whole table as table_temp, and then use:

Left join(..)

Load

...

Resident table_temp

Where

from_task='Start'

and to_task='Build';