Hi Experts,
Please help me to get this desired output.
I want to create a NewID column from T2 table which will counter ID1 first then ID2.
let suppose ID1 is Product
ID2 is financial year
I have tried too many combinations in resident but still searching for desired output shown shown below.
NewID is the required field.
| ID1 |
ID2 |
NewID |
Value |
| A |
Y1 |
1 |
460 |
| A |
Y1 |
1 |
550 |
| A |
Y2 |
2 |
640 |
| A |
Y2 |
2 |
800 |
| B |
Y1 |
1 |
310 |
| B |
Y1 |
1 |
480 |
| B |
Y2 |
2 |
380 |
| B |
Y2 |
2 |
670 |
| C |
Y1 |
1 |
230 |
| C |
Y2 |
2 |
320 |
| C |
Y2 |
2 |
550 |
| C |
Y3 |
3 |
410 |
T1:
Load * Inline [
ID1,ID2, Value
A, Y1, 460
A, Y1, 550
A, Y2, 640
A, Y2, 800
B, Y1, 480
B, Y1, 310
B, Y2, 670
B, Y2, 380
C, Y1, 230
C, Y2, 550
C, Y2, 320
C, Y3, 410
];
T2:
Load *,
IF(
ID1<>Previous(ID1),
1,
AutoNumber(ID1&'-'&ID2)
) as NewID10,
IF(
ID1<>Previous(ID1),
1,
AutoNumber(RecNo(),ID1&'-'&ID2)
) as NewID9,
IF(
ID1<>Previous(ID1),
1,
AutoNumber(RowNo(),ID1&'-'&ID2)
) as NewID11,
AutoNumber(RowNo(),ID1) as NewID1,
AutoNumber(RecNo(),ID1&'-'&ID2) as NewID2,
AutoNumber(ID2,ID1) as NewID3,
AutoNumber(ID1,ID2) as NewID4,
AutoNumber(ID1,RowNo()) as NewID5,
AutoNumber(ID1,'ID') as NewID6, // same as AutoNumber(ID) as NewID2
AutoNumber(RowNo(),ID2) as NewID7,
AutoNumber(RowNo(),ID1&'-'&ID2) as NewID8
Resident T1
order by ID1,ID2
asc
;
Drop Table T1;
Exit Script;
