Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
what is this use and how it will work can anyone explain?
AutoNumber(Trim(identifier&code&line&date &product&manufac&product_id&channel)) as "_KEY";
Hi soniasweety
Trim() removes the spaces before and after the value of the filed.
Trim( ' abc' ) Returns 'abc'
Trim( 'abc ' ) Returns 'abc'
Trim( ' abc ' ) Returns 'abc'
Autonumber will give numeric values from 1 to number of values of the field.
We can generate serial numbers by concatenating/joing more than one field. For example as your KEY filed.
Example :
Autonumber(Field1&Field2&Field3) as Key
RegionSales: LOAD *, AutoNumber(Region&Year&Month) as RYMkey; LOAD * INLINE [ Region, Year, Month, Sales North, 2014, May, 245 North, 2014, May, 347 North, 2014, June, 127 South, 2014, June, 645 South, 2013, May, 367 South, 2013, May, 221 ];
The resulting table looks like this:
Region | Year | Month | Sales | RYMkey |
---|---|---|---|---|
North | 2014 | May | 245 | 1 |
North | 2014 | May | 347 | 1 |
North | 2014 | June | 127 | 2 |
South | 2014 | June | 645 | 3 |
South | 2013 | May | 367 | 4 |
South | 2013 | May | 221 |
Hi soniasweety
Trim() removes the spaces before and after the value of the filed.
Trim( ' abc' ) Returns 'abc'
Trim( 'abc ' ) Returns 'abc'
Trim( ' abc ' ) Returns 'abc'
Autonumber will give numeric values from 1 to number of values of the field.
We can generate serial numbers by concatenating/joing more than one field. For example as your KEY filed.
Example :
Autonumber(Field1&Field2&Field3) as Key
RegionSales: LOAD *, AutoNumber(Region&Year&Month) as RYMkey; LOAD * INLINE [ Region, Year, Month, Sales North, 2014, May, 245 North, 2014, May, 347 North, 2014, June, 127 South, 2014, June, 645 South, 2013, May, 367 South, 2013, May, 221 ];
The resulting table looks like this:
Region | Year | Month | Sales | RYMkey |
---|---|---|---|---|
North | 2014 | May | 245 | 1 |
North | 2014 | May | 347 | 1 |
North | 2014 | June | 127 | 2 |
South | 2014 | June | 645 | 3 |
South | 2013 | May | 367 | 4 |
South | 2013 | May | 221 |
In simple words
It is creating a unique key by combining multiple columns.
It may be because, there no column value is unique.
then using this key to join with other tables.
It is better to have join key as numeric to improve the performance, so it is using Autonumber function to create the numeric value for combined column values.
Trim function to trim the values (remove the space).