Derive New Field

[alvinford]

Hi Struck with the basic thing . I have the following table

StarDate	EndDate	ID
01/01/2010	31/12/2012	540
01/05/2012	31/12/2012	540
01/05/2012	31/12/2012	540
01/05/2012	31/12/2012	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540
01/01/2013	28/02/2013	540

I want to create new column based on my EndDate. Want to group the values when EndDate is Same as shown below.

StarDate	EndDate	ID	Group
01/01/2010	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/05/2012	31/12/2012	540	Group1
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2
01/01/2013	28/02/2013	540	Group2

Need your help to achieve this.

Regards,

Alvin.

[#2 · Accepted solution]

Hi Alvin,

U can write a Script like Below

Tb1:

Load Distinct

ID

,Start_Date,End_Date
,'Group'&Text(AutoNumber(End_Date,ID)) as Group;

Load *  Inline

[Start_Date,End_Date,ID

01/01/2010,31/12/2012,540

01/05/2012,31/12/2012,540

01/05/2012,31/12/2012,540

01/05/2012,31/12/2012,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

01/01/2013,28/02/2013,540

24/12/2010,23/12/2012,250

01/05/2012,23/12/2012,250

01/05/2012,23/12/2012,250 

01/05/2012,23/12/2012,250

24/12/2012,29/02/2013,250

24/12/2012,29/02/2013,250

01/03/2012,31/08/2013,250

01/03/2013,31/08/2013,250

];

Regards,

Kabilan K.

[alvinford]

Hi Richard,

May be I have not stated it correctly. I have the following Fields.

ID	StartDate	EndDate
540	01/01/2010	31/12/2012
540	01/05/2012	31/12/2012
540	01/05/2012	31/12/2012
540	01/05/2012	31/12/2012
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
540	01/01/2013	28/02/2013
250	24/12/2010	23/12/2012
250	01/05/2012	23/12/2012
250	01/05/2012	23/12/2012
250	01/05/2012	23/12/2012
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	24/12/2012	28/02/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013
250	01/03/2013	31/08/2013

I have to derive a new field Group. I have to group the EndDate for each ID as shown below

ID	StartDate	EndDate	Group
540	01/01/2010	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/05/2012	31/12/2012	Group1
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
540	01/01/2013	28/02/2013	Group2
250	24/12/2010	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	01/05/2012	23/12/2012	Group1
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	24/12/2012	28/02/2013	Group2
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3
250	01/03/2013	31/08/2013	Group3

For Example for the ID 540 there are two repeated values in the EndDate the First Value is grouped as Group1 and the Second Value is Grouped as Group2. For the ID 250 there are three repeated values in the EndDate so in the Group Column there are three values in the Group i.e. Group1,Group2 and Group3.

Need your assistance .

Regards,

Alvin.

[#4]

Hi,

derive field =

'Group'&Text(AutoNumber(End_Date,ID)) as Group;

Pls see the attachment

[rlp]

You can generate an indice with RecNo() but, given that your index must be generated for each ID, you should iterate on the ID. For that, you should first isolate yout diiferents ID and then construct your table for each ID.

This gives the following code:

tmp_ID:

NOConcatenate

LOAD DISTINCT

     ID

RESIDENT <your_table> ;

FOR ID_indice=0 TO NOOfRows( 'tmp_ID' )

     SET current_ID = peek( 'ID' , $(#ID_indice) , 'tmp_ID' ) ;

     table_to_join:

     LOAD DISTINCT

          ID ,

          EndDate ,

          'Group' & RowNo() as Group

     RESIDENT <your_table>

     WHERE ID='current_ID' ;   

NEXT ID_indice ;

DROP TABLE tmp_ID ;

LEFT JOIN( <your_table> )

LOAD DISTINCT

     *

RESIDENT table_to_join ;

DROP TABLE table_to_join ;

[rlp]

I forgot to order by dates so the code for table_to_join should be:

table_to_join:

LOAD DISTINCT

     ...

RESIDENT <your_table>

     WHERE ID='$(current_ID)'

     ORDER BY EndDate ASC

;

[rwunderlich]

I think Kabilan has it right. You want a set of counters for each ID, correct?

autonumber(EbdDate,ID)

will use a seperate pool of numbers for each ID.

-Rob