Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I'm looking for inputs in creating a new Counter Field (Probably can be done with RowNo()) based on multiple other fields in Load Script part.
My Input data looks as below:
ID | Place | Entry | Exit |
0B81432 | SPAP | - | 17.01.2019 12:45 |
0B99873 | MUCG | 18.01.2019 13:43 | |
0B81432 | CHSP | 19.01.2019 08:35 | - |
0B81432 | CHSP | - | 24.01.2019 13:36 |
0B81432 | SPAP | 24.01.2019 13:36 | - |
0B81432 | SPAP | - | 18.03.2019 17:51 |
0B81432 | SAVP | 24.09.2019 09:09 | - |
0B81432 | SAVP | - | 26.09.2019 06:38 |
0B81432 | CHSP | 26.09.2019 06:38 | - |
0B81432 | CHSP | - | 04.10.2019 22:00 |
0B81432 | BRVP | 21.10.2019 09:04 | - |
0B81432 | BRVP | - | 27.02.2020 12:36 |
0B81432 | BRVP | 28.02.2020 09:19 | - |
0B81432 | BRVP | - | 08.03.2020 23:00 |
0B81432 | DURP | 12.04.2020 14:43 | - |
0B81432 | DURP | - | 15.04.2020 15:55 |
0B99873 | DURP | 12.04.2020 14:43 | - |
0B99873 | DURP | - | 19.04.2020 15:55 |
0B99873 | SAVP | 29.04.2020 15:55 | - |
Based on this, i would like to add a Counter for each ID, Place combination.
My expected result would be the below table:
ID | Place | Entry | Exit | Event Number |
0B81432 | SPAP | - | 17.01.2019 12:45 | 0 |
0B99873 | MUCG | 18.01.2019 13:43 | 0 | |
0B81432 | CHSP | 19.01.2019 08:35 | - | 1 |
0B81432 | CHSP | - | 24.01.2019 13:36 | 1 |
0B81432 | SPAP | 24.01.2019 13:36 | - | 2 |
0B81432 | SPAP | - | 18.03.2019 17:51 | 2 |
0B81432 | SAVP | 24.09.2019 09:09 | - | 3 |
0B81432 | SAVP | - | 26.09.2019 06:38 | 3 |
0B81432 | CHSP | 26.09.2019 06:38 | - | 4 |
0B81432 | CHSP | - | 04.10.2019 22:00 | 4 |
0B81432 | BRVP | 21.10.2019 09:04 | - | 5 |
0B81432 | BRVP | - | 27.02.2020 12:36 | 5 |
0B81432 | BRVP | 28.02.2020 09:19 | - | 6 |
0B81432 | BRVP | - | 08.03.2020 23:00 | 6 |
0B81432 | DURP | 12.04.2020 14:43 | - | 7 |
0B81432 | DURP | - | 15.04.2020 15:55 | 7 |
0B99873 | DURP | 12.04.2020 14:43 | - | 2 |
0B99873 | DURP | - | 19.04.2020 15:55 | 2 |
0B99873 | SAVP | 29.04.2020 15:55 | - | 3 |
I've tried using the RowNo() option in Load Script but, i didn't workout as expected.
Any pointers would be highly appreciated.
Thanks
Sai
May be try this
Table:
LOAD * INLINE [
ID, Place, Entry, Exit
0B81432, SPAP, -, 17.01.2019 12:45
0B99873, MUCG, -, 18.01.2019 13:43
0B81432, CHSP, 19.01.2019 08:35, -
0B81432, CHSP, -, 24.01.2019 13:36
0B81432, SPAP, 24.01.2019 13:36, -
0B81432, SPAP, -, 18.03.2019 17:51
0B81432, SAVP, 24.09.2019 09:09, -
0B81432, SAVP, -, 26.09.2019 06:38
0B81432, CHSP, 26.09.2019 06:38, -
0B81432, CHSP, -, 04.10.2019 22:00
0B81432, BRVP, 21.10.2019 09:04, -
0B81432, BRVP, -, 27.02.2020 12:36
0B81432, BRVP, 28.02.2020 09:19, -
0B81432, BRVP, -, 08.03.2020 23:00
0B81432, DURP, 12.04.2020 14:43, -
0B81432, DURP, -, 15.04.2020 15:55
0B99873, DURP, 12.04.2020 14:43, -
0B99873, DURP, -, 19.04.2020 15:55
0B99873, SAVP, 29.04.2020 15:55, -
];
FinalTable:
LOAD *,
If(ID = Previous(ID), If(Place = Previous(Place), Peek('Event Number'), RangeSum(Peek('Event Number'), 1)), 0) as [Event Number]
Resident Table
Order By ID;
DROP Table Table;
May be try this
Table:
LOAD * INLINE [
ID, Place, Entry, Exit
0B81432, SPAP, -, 17.01.2019 12:45
0B99873, MUCG, -, 18.01.2019 13:43
0B81432, CHSP, 19.01.2019 08:35, -
0B81432, CHSP, -, 24.01.2019 13:36
0B81432, SPAP, 24.01.2019 13:36, -
0B81432, SPAP, -, 18.03.2019 17:51
0B81432, SAVP, 24.09.2019 09:09, -
0B81432, SAVP, -, 26.09.2019 06:38
0B81432, CHSP, 26.09.2019 06:38, -
0B81432, CHSP, -, 04.10.2019 22:00
0B81432, BRVP, 21.10.2019 09:04, -
0B81432, BRVP, -, 27.02.2020 12:36
0B81432, BRVP, 28.02.2020 09:19, -
0B81432, BRVP, -, 08.03.2020 23:00
0B81432, DURP, 12.04.2020 14:43, -
0B81432, DURP, -, 15.04.2020 15:55
0B99873, DURP, 12.04.2020 14:43, -
0B99873, DURP, -, 19.04.2020 15:55
0B99873, SAVP, 29.04.2020 15:55, -
];
FinalTable:
LOAD *,
If(ID = Previous(ID), If(Place = Previous(Place), Peek('Event Number'), RangeSum(Peek('Event Number'), 1)), 0) as [Event Number]
Resident Table
Order By ID;
DROP Table Table;