Hi,
I am trying to generate sequence number based on the date field values using above()function. But I am not getting right.
Here is the test data:
| Line | ProductionDate | System1_Seq | Module | Planned | Actual |
| H | 11/11/2011 | 0 | 18452028 | 4/1/2019 9:23:36 AM | |
| H | 11/11/2011 | 0 | 32804298 | 4/2/2019 9:23:36 AM | |
| H | 4/29/2019 | 1 | 23088554 | 4/25/2019 9:23:36 AM | 4/26/2019 10:32:18 AM |
| H | 4/29/2019 | 2 | 39984725 | 4/25/2019 9:55:03 AM | 4/26/2019 12:41:49 PM |
| H | 4/29/2019 | 3 | 29597737 | 4/25/2019 10:16:30 AM | 4/26/2019 12:42:00 PM |
| H | 4/29/2019 | 4 | 25580171 | 4/25/2019 10:37:57 AM | 4/26/2019 12:42:11 PM |
| H | 4/29/2019 | 5 | 20829920 | 4/25/2019 10:59:24 AM | 4/29/2019 6:50:51 AM |
| H | 4/30/2019 | 1 | 21358159 | 4/26/2019 10:16:30 AM | 4/29/2019 10:51:12 AM |
| H | 4/30/2019 | 2 | 39709921 | 4/26/2019 10:37:57 AM | |
| H | 4/30/2019 | 3 | 15893986 | 4/26/2019 10:59:24 AM | |
| H | 4/30/2019 | 4 | 26933388 | 4/26/2019 11:20:51 AM | |
| H | 4/30/2019 | 5 | 26057444 | 4/26/2019 11:42:18 AM | |
| H | 5/1/2019 | 1 | 7766146 | 4/29/2019 11:20:51 AM | |
| H | 5/1/2019 | 2 | 33993189 | 4/29/2019 11:42:18 AM | |
| H | 5/1/2019 | 3 | 9917204 | 4/29/2019 12:03:45 PM | |
| H | 5/1/2019 | 4 | 5321326 | 4/29/2019 12:55:12 PM | |
| H | 5/1/2019 | 5 | 12131044 | 4/29/2019 1:16:39 PM | |
| H | 5/2/2019 | 1 | 9046633 | 4/30/2019 11:42:18 AM | |
| H | 5/2/2019 | 2 | 37038467 | 4/30/2019 12:03:45 PM | |
| H | 5/2/2019 | 3 | 30133340 | 4/30/2019 12:55:12 PM | |
| H | 5/2/2019 | 4 | 20614715 | 4/30/2019 1:16:39 PM | |
| H | 5/2/2019 | 5 | 29616535 | 4/30/2019 2:22:00 PM | |
So from above data,
1. One calculated field is Date: Expression for it is: "if(isnull(Actual), Planned, Actual)
2. Based on the Date field above, i want generate sequence number against each production date.
so the result should look like the following:
| Line | ProductionDate | System1_Seq | Module | Planned | Actual | Date | Seq |
| H | 11/11/2011 | 0 | 18452028 | 4/1/2019 9:23:36 AM | | 4/1/2019 9:23:36 AM | 0 |
| H | 11/11/2011 | 0 | 32804298 | 4/2/2019 9:23:36 AM | | 4/2/2019 9:23:36 AM | 0 |
| H | 4/29/2019 | 1 | 23088554 | 4/25/2019 9:23:36 AM | 4/26/2019 10:32:18 AM | 4/26/2019 10:32:18 AM | 1 |
| H | 4/29/2019 | 2 | 39984725 | 4/25/2019 9:55:03 AM | 4/26/2019 12:41:49 PM | 4/26/2019 12:41:49 PM | 2 |
| H | 4/29/2019 | 3 | 29597737 | 4/25/2019 10:16:30 AM | 4/26/2019 12:42:00 PM | 4/26/2019 12:42:00 PM | 3 |
| H | 4/29/2019 | 4 | 25580171 | 4/25/2019 10:37:57 AM | 4/26/2019 12:42:11 PM | 4/26/2019 12:42:11 PM | 4 |
| H | 4/29/2019 | 5 | 20829920 | 4/25/2019 10:59:24 AM | 4/29/2019 6:50:51 AM | 4/29/2019 6:50:51 AM | 5 |
| H | 4/30/2019 | 1 | 21358159 | 4/26/2019 10:16:30 AM | 4/29/2019 10:51:12 AM | 4/29/2019 10:51:12 AM | 1 |
| H | 4/30/2019 | 2 | 39709921 | 4/26/2019 10:37:57 AM | | 4/26/2019 10:37:57 AM | 2 |
| H | 4/30/2019 | 3 | 15893986 | 4/26/2019 10:59:24 AM | | 4/26/2019 10:59:24 AM | 3 |
| H | 4/30/2019 | 4 | 26933388 | 4/26/2019 11:20:51 AM | | 4/26/2019 11:20:51 AM | 4 |
| H | 4/30/2019 | 5 | 26057444 | 4/26/2019 11:42:18 AM | | 4/26/2019 11:42:18 AM | 5 |
| H | 5/1/2019 | 1 | 7766146 | 4/29/2019 11:20:51 AM | | 4/29/2019 11:20:51 AM | 1 |
| H | 5/1/2019 | 2 | 33993189 | 4/29/2019 11:42:18 AM | | 4/29/2019 11:42:18 AM | 2 |
| H | 5/1/2019 | 3 | 9917204 | 4/29/2019 12:03:45 PM | | 4/29/2019 12:03:45 PM | 3 |
| H | 5/1/2019 | 4 | 5321326 | 4/29/2019 12:55:12 PM | | 4/29/2019 12:55:12 PM | 4 |
| H | 5/1/2019 | 5 | 12131044 | 4/29/2019 1:16:39 PM | | 4/29/2019 1:16:39 PM | 5 |
| H | 5/2/2019 | 1 | 9046633 | 4/30/2019 11:42:18 AM | | 4/30/2019 11:42:18 AM | 1 |
| H | 5/2/2019 | 2 | 37038467 | 4/30/2019 12:03:45 PM | | 4/30/2019 12:03:45 PM | 2 |
| H | 5/2/2019 | 3 | 30133340 | 4/30/2019 12:55:12 PM | | 4/30/2019 12:55:12 PM | 3 |
| H | 5/2/2019 | 4 | 20614715 | 4/30/2019 1:16:39 PM | | 4/30/2019 1:16:39 PM | 4 |
| H | 5/2/2019 | 5 | 29616535 | 4/30/2019 2:22:00 PM | | 4/30/2019 2:22:00 PM | 5 |
Can someone help me get this result.
I have attached test qvw file.
Thanks,
Vidya
