First Record

[upaliwije]

Dear Friends

I have following data in a my data table

Policy_No     Month     

1                    1

1                    2

1                    4

1                    3

2                    2

2                    3

2                    5

3                    4

3                    5

3                    6

3                    7

I want to write a syntax in data load to obtain 1st month of each policy Eg Following format

Policy_No     Month      1st Month

1                    1               1

1                    2

1                    4

1                    3

2                    2               2

2                    3

2                    5

3                    4               4

3                    5

3                    6

3                    7

PLS HELP ME TO WRITE THE SYNTAX

[ali_hijazi]

after loading your table write the following:

temp:

load Policy_no, min(Month) as Month

resident your_table

group by Policy_No;

left join(your_table)

load * resident temp;

drop table temp;

[maxgro]

a:

load * inline [

Policy_No ,    Month    

1         ,           1

1         ,           2

1         ,           4

1         ,           3

2          ,          2

2           ,         3

2           ,         5

3           ,         4

3           ,         5

3           ,         6

3           ,         7

];

b:

NoConcatenate load

  *,

  if(Policy_No <> Peek(Policy_No), Month) as Flag 

Resident a order by  Policy_No ,    Month;

DROP Table a;

[sunny_talwar]

Is this the desired output?

If yes then look at the script and also find attached a sample app.

Table:

LOAD * INLINE [

    Policy_No, Month

    1, 1

    1, 2

    1, 4

    1, 3

    2, 2

    2, 3

    2, 5

    3, 4

    3, 5

    3, 6

    3, 7

];

Join(Table)

LOAD Policy_No,

  FirstValue(Month) as [1st Month]

Resident Table

Group By Policy_No

Order By Policy_No, Month;

Best,

Sunny

[anbu1984]

T1:
Load * Inline [
Policy_No,Month    
1,1
1,2
1,4
1,3
2,2
2,3
2,5
3,4
3,5
3,6
3,7 ];

NoConcatenate
Load *,If(AutoNumber(RecNo(),Policy_No)=1,Month) Resident T1 Order By Policy_No,Month;

Drop Table T1;

[upaliwije]

Thanks  for your reply. Your syntax is correct for my requirement. But my require differ little bit I want 1st month against all records as follows

Policy_No     Month      1st Month

1                    1               1

1                    2               1

1                    4               1

1                    3               1

[maxgro · Accepted solution]

this?

a:

load * inline [

Policy_No ,    Month   

1        ,          1

1        ,          2

1        ,          4

1        ,          3

2          ,          2

2          ,        3

2          ,        5

3          ,        4

3          ,        5

3          ,        6

3          ,        7

];

b:

NoConcatenate load

  Policy_No, Month,

  if(isnull(Flag), Peek(Flag), Flag) as Flag;

NoConcatenate load

  *,

  if(Policy_No <> Peek(Policy_No), Month) as Flag

Resident a order by  Policy_No ,    Month;

DROP Table a;

[upaliwije]

Thanks all