topic Re: Load only Top 1 record ordered by date in QlikView

Load only Top 1 record ordered by date

Fri, 13 Jan 2012 21:54:24 GMT

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

Re: Load only Top 1 record ordered by date

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 22:33:14 GMT

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

Load only Top 1 record ordered by date

Mon, 16 Jan 2012 21:24:25 GMT

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

Re: Load only Top 1 record ordered by date

marcelo_7 — Wed, 08 Aug 2012 14:17:38 GMT

Try the function FirstSortedValue().

Re: Load only Top 1 record ordered by date

Wed, 08 Aug 2012 15:23:04 GMT

hi,

Please see solution attached.

Regards

Rahul