Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load only Top 1 record ordered by date

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 NumberRegistrationDateNew UserUserNameCompanyName
11/1/2011YU1A
21/10/2011YU2B
31/19/2011NU1A
41/28/2011YU3A
52/6/2011YU4C
62/15/2011NU4C
72/24/2011YU5B
83/5/2011NU5B
93/14/2011YU6B
103/23/2011YU7C
114/1/2011NU1A
124/10/2011YU8D
134/19/2011NU8D
144/28/2011YU9E
155/7/2011YU10E
Required Table (NewCustomer)
Serial NumberRegistrationDateNew CompanyCompany name
11/1/2011YA
21/10/2011YB
52/6/2011YC
124/10/2011YD
144/28/2011YE

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

4 Replies
Miguel_Angel_Baeyens
Not applicable

Re: Load only Top 1 record ordered by date

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

Not applicable

Load only Top 1 record ordered by date

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
Not applicable

Re: Load only Top 1 record ordered by date

Try the function FirstSortedValue().

Not applicable

Re: Load only Top 1 record ordered by date

hi,

Please see solution attached.

Regards

Rahul