Load only Top 1 record ordered by date

[#1]

Hi All,

The below table has New User column set to 'Y' for the very first serialnumber that every user registers. With this we can get how many new users registered our products. However the requirement is to get how many new companys registered our products.So for that I need the second table (NewCustomer) as shown below...

Existing Table (RegistrationTemp)
Serial Number	RegistrationDate	New User	UserName	CompanyName
1	1/1/2011	Y	U1	A
2	1/10/2011	Y	U2	B
3	1/19/2011	N	U1	A
4	1/28/2011	Y	U3	A
5	2/6/2011	Y	U4	C
6	2/15/2011	N	U4	C
7	2/24/2011	Y	U5	B
8	3/5/2011	N	U5	B
9	3/14/2011	Y	U6	B
10	3/23/2011	Y	U7	C
11	4/1/2011	N	U1	A
12	4/10/2011	Y	U8	D
13	4/19/2011	N	U8	D
14	4/28/2011	Y	U9	E
15	5/7/2011	Y	U10	E

Required Table (NewCustomer)
Serial Number	RegistrationDate	New Company	Company name
1	1/1/2011	Y	A
2	1/10/2011	Y	B
5	2/6/2011	Y	C
12	4/10/2011	Y	D
14	4/28/2011	Y	E

I would need something as below ...

NewCustomer:

LOAD

Top1 serialnumber,
'Yes' AS NewCustomer
RESIDENT
RegistrationTemp WHERE NewUser = 'Yes'
GROUP BY CompanyName

ORDER By RegistrationDate ASC;

Will wait for your inputs.

Thanks

Shilpa

[Miguel_Angel_Baeyens]

Hi Shilpa,

I'd create a new chart in your dashboard, with Serial Number, RegistrationDate and New User as dimensions (label them as you want) and this as the expression that returns the company name:

Only({< [New User] = {'Y'} >} If(Aggr(NODISTINCT Min({< [New User] = {'Y'} >} RegistrationDate), CompanyName) = RegistrationDate, CompanyName)) 

Hope that helps.

Miguel

[#3]

Hi Miguel,

Thank you for the reply.

I need New Customer column as a flag ( and at the script level ). The idea is to use this column as a global filter to filter the values of the reports on the tab.

Cant we have a load statement to load the top 1 record only ?

Thanks

Shilpa

[marcelo_7]

Try the function FirstSortedValue().

[#5]

hi,

Please see solution attached.

Regards

Rahul