Issue with firstsortedvalue

[#1]

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

[anbu1984]

What is your where clause?

[Anonymous]

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

LOAD

...,

ID as UserID;

SQL SELECT

...,

ID

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.

[#4]

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

[#5]

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.

[anbu1984]

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.

[#7]

Hi Anbu,

Left JOin(MasterTable)

Load

     Bundle,

     ID as UserID

From table.qvd

where from_task='Start'

and to_task='Build';

[anbu1984]

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

[#9]

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

Yes it will not affect.

[sasikanth]

HI

Try the same in Resident load , that may work