Use of Autonumber - what am I doing wrong?

[josephinetedesc]

Hi All

I am trying to work my way around the function Autonumber - worked beautifully for another example - but for this example strange results. 

I want the

autonumber to be  1     for John

autonumber to be 1     for George

autonumber to be 2      for  george

autonumber to be 1     for Paul

autonumber to be1      for Ringo

I have showed the code I used - what am I doing wrong? 
The count([Date Appearance]) is always correct it shown that george has 2, the rest one.

I read the help files but ...

autonumber(expression [, index])

What is my expression in this case?  are the square brackets causing problems?

Thank you for any help with this problem.

Jo

Original File
Name Artist	Date Appearance
John	27/02/2015
George	6/04/2009
George	11/05/2009
Paul	26/03/2010
Ringo	7/08/2014
code used to get ValidType
ValidKey	Name Artist	Date Appearance	ValidID	ValidType	ValidatedDate:
27/02/2015John	John	27/02/2015	1	Valid 2	LOAD
06/04/2009George	George	6/04/2009	2	Valid 2	distinct
11/05/2009Geirge	George	11/05/2009	3	Valid 3	RecNo() as ValidID,
26/03/2010Paul	Paul	26/03/2010	4	Valid 2	date([Date Appearance],'dd/MM/yyyy')& [Name Artist] as ValidKey,
07/08/2014Ringo	Rngo	7/08/2014	5	Valid 3	[Name Artist],
					date([Date Appearance],'dd/MM/yyyy')as [Date Appearance],
					Valid '& AutoNumber([Date Appearance]& [Name Artist] , Hash128([Name Artist]  ) )as ValidType
					FROM [XXX.xlsx](ooxml, embedded labels, table is Sheet2);
					code used to get ValidType
ValidKey	Name Artist	Date Appearance	ValidID	ValidType	Table5:
27/02/2015John	John	27/02/2015	1	Valid 1	load
06/04/2009George	George	6/04/2009	2	Valid 1	distinct
11/05/2009George	George	11/05/2009	3	Valid 1	RecNo() as ValidID,
26/03/2010Paul	Paul	26/03/2010	4	Valid 1	date([Date Appearance],'dd/MM/yyyy')& [Name Artist] as ValidKey,
07/08/2014Ringo	Ringo	7/08/2014	5	Valid 1	[Name Artist],
					[Date Appearance],
					Valid '& AutoNumber(RecNo(),[Name Artist]&[Date Appearance]) as ValidType
					FROM [XXX.xlsx](ooxml, embedded labels, table is Sheet2);

[swuehl]

Try

Valid '& AutoNumber(RecNo(),[Name Artist] ) as ValidType

so you create a counter for every [Name Artist] value.

Your first output seems a bit strange though, I would expect to see Valid Type 1.

edit: except you are running the same Autonumber() code in an unshown code snippet preceding the shown one.

[MK_QSL · Accepted solution]

Data:
Load
 [Date Appearance] & [Name Artist] as ValidKey
 ,*
;
Load
 [Name Artist] as [Name Artist],
 Date(Date#([Date Appearance],'D/MM/YYYY')) as [Date Appearance]
Inline
[
 Name Artist, Date Appearance 
 John, 27/02/2015 
 George, 6/04/2009 
 George, 11/05/2009 
 Paul, 26/03/2010 
 Ringo, 7/08/2014
 Ringo, 7/09/2014
];

Left Join (Data)
Load 
 ValidKey, 
 'Valid ' & AutoNumber([Name Artist]&[Date Appearance],[Name Artist]) as ValidID
Resident Data
Order By [Name Artist],[Date Appearance]
;

[YoussefBelloum]

Hi swuehl‌

Name_artist is case sensitive inside the Autonumber ?

[josephinetedesc]

Hi Mannish

this works -

Why do I need to do a separate load here?

1. Data: 
2. Load 
3. [Date Appearance] & [Name Artist] as ValidKey 
4. ,* 
5. ; 

[MK_QSL]

1) I used preceding load, assuming that your data is coming through QVDs. If we do ValidKey creation using & then your QVD load is unoptimised load.

2) 2nd Join is used because I assumed that you want to give ValidID for each Name but sorting order of Date. Means 1 for older date and 2 for newer date and so on. So we need to do Order By Clause.

Hope this make sense.

[josephinetedesc]

There is something wrong ... not sure it is to do with autonumber ... works perfectly when I I do this in an independent qvw file - but when I add the code to a sheet in a qvw file - it does not work ...

so I think it will be a long night

When I put it back to a sheet in the qvw file - it works like the first example (no autonumber 1 and then 2,3 etc)

Thank you Mannish

Jo

[josephinetedesc]

The original load is from an excel file ...

so:

1. Data:  Load  [Date Appearance] & [Name Artist] as ValidKey  ,*  ; 
2. Load  [Name Artist] and [Date Appearance] from the excel file (this is the preceding load)

               3. do a left Join (based on the Valid Key)