Adding a financial year Counter for each distinct Product
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;
