I have fields AssetSerialNumber (points to the product serial number the customer has), CaseCreatedDate, CaseNumber,etc.
I need to have a report that has a new field named "Repeat_Caller_Flag" that says 1 if the AssetSerialNumber is repeated more than once in the table. However, the first instance should always be marked 0 and after the first instance if there are more then they should be all be marked as 1.
I have this code below but it is taking painfully long to execute. Can someone suggest a better option?
Seq:
load *, If("Case Asset Serial Number"=Previous("Case Asset Serial Number") If("Case Asset Serial Number"=Previous("Case Asset Serial Number"),Peek(Repete_Seq)+1,1) as Repete_Seq Resident Case order by "Case Asset Serial Number",[Case Created Timestamp] desc;
drop table Case;
NoConcatenate
Seq_1:
load "Case Asset Serial Number",if(max(Repete_Seq)>1,1,0) as Repeate_Caller_Flag resident Seq group by "Case Asset Serial Number";
If you are only doing the PREVIOUS/PEEK to create the counter, but don't really need that field for the end user you could simply do something like this and skip your code:
Numbers:
Load
"Case Asset Serial Number",
IF (AssetCount = 1, 'Single', 'Multiple') as AssetCountFlag;
Load
"Case Asset Serial Number",
Count("Case Asset Serial Number") as AssetCount
Resident Case
Group by "Case Asset Serial Number";
Alternatively you could simply do an IF when doing the count to say Single/Multiple if you don't actually want to keep the count as a field. I did preceding load to give you the flag and the count.
Hi, thanks for the code. However, it is still not giving me the first instance of the asset serial number as 0. I have replaced 'Single' and 'Multiple' with 0 and 1 below. The serial number that is present only once should show 0 but the first instance of the serial number that is present multiple times should also show 0 (the remaining ones,after the first instance, should show 1). I could arrange the serial numbers by ascending or descending order of case creation.
Hi, thanks to both of you. However, I have written a code myself and I feel it is close to working but it is not working apart from giving me a synthetic key warning. I will once again show what I have and what I want.
I am avoiding using Peek because with the amount of data I have, it is taking a long time to execute.
The code I have written for creating Case2 from Case1 is given below but it is giving me a synthetic key warning plus it is not working. Can anyone point out what is wrong?
The data is ordered by AssetSerialNumber and CaseOpenDate in ascending order. What the code should do is to check if an AssetSerialNumber is different from its previous one (it means a new asset serial begins and this asset could be in 1 row only (marked as 0) or more than 1 row (multiple occurrences so marked 1). However, even if there are multiple occurrences, the first one must be marked as 0. The code checks the previous asset and also checks if the assetcountflag is 1 (meaning it is present in other rows as well). If it is 1, it changes it to 0 (thus, the first instance, among multiple ones, becomes 0).
Load *, if(Case_Flat_Case.Asset_Serial_Number <> Previous(Case_Flat_Case.Asset_Serial_Number) and "AssetCountFlag"=1, "AssetCountFlag" = 0) Resident Case1 order by Case_Flat_Case.Asset_Serial_Number,Case_Flat_Case.Date_Time_Opened asc;
Hi, thanks to both of you. However, I have written a code that is close to working but is not quite working and is giving a synthetic key error. Also, I am not too keen to use PEEK because with the kind of data I have is taking a long time.
I have written this code below that checks the AssetSerialNumber with the previous AssetSerialNumber. If it is not matching, it means it is a "new" asset. If this asset is present in only one 1 row, then AssetCountFlag will be 0. If it is present in more than 1 row, then the AssetCountFlag is 1 (in all rows). However, my requirement is that the AssetCountFlag should be 0 for the first row even if that asset is present in more than 1 row. So in a nutshell, the AssetCountFlag should be 0 when 1) the asset is present in only one row 2) the first row of the asset even when it is present in multiple rows. In all the next rows, this flag needs to show 1 (I already have this and only the first instance is a problem). The code below is giving me a synthetic key warning and does not seem to work. Can anyone please point out and correct it?
Load *, if(Case_Flat_Case.Asset_Serial_Number <> Previous(Case_Flat_Case.Asset_Serial_Number) and "AssetCountFlag"=1, "AssetCountFlag" = 0) Resident Case1 order by Case_Flat_Case.Asset_Serial_Number,Case_Flat_Case.Date_Time_Opened asc;
