Hi,
I have fields as stated below. When the AssetSerial is present only once, the AssetCountFlag is 0 but when it is present more than once the AssetCountFlag is 1. However, since the data is in ascending order of case open date, the first instance (the oldest case open data) should show 0 and then the repeating AssetSerial can show 1. Please see the code that I have written to make the first instance 0.
Case4:
Load *, if(Case_Flat_Case.Asset_Serial_Number <> Previous(Case_Flat_Case.Asset_Serial_Number),
0, 1) as AssetCountFinalFlag (new flag I am creating)
Resident Case1
Order By Case_Flat_Case.Asset_Serial_Number,Case_Flat_Case.Date_Time_Opened asc;
When this code runs I find that AssetSerials that are present even once are getting duplicated with AssetCountFinalFlag as 0 in the first row and 1 in the next row. Also, the first row of the AssetSerialNumbers (more than one present) is getting duplicated with 0 in the first row and 1 in the next rows. The next rows are all coming okay. Can anyone tell me why this is happening and how I can rectify this?
AssetSerial AssetCountFlag Case Open Date(in asc order)
ASD4567 1 should become 0
ASD4567 1
ASD4567 1
ASD4567 1
AWE7899 0
AKL9000 0
AZX3478 1 should become 0
AZX3478 1
