Generate Sequence Number Based on Date Field Value

poluvidyasagar — Sat, 16 Nov 2024 20:57:19 GMT

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

topic Generate Sequence Number Based on Date Field Value in QlikView

Generate Sequence Number Based on Date Field Value