topic Re: Creating a sequence counter in App Development

Creating a sequence counter

seraphis — Tue, 21 Dec 2021 21:56:36 GMT

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";

Re: Creating a sequence counter

Dalton_Ruer — Wed, 12 May 2021 14:18:56 GMT

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.

Re: Creating a sequence counter

seraphis — Thu, 13 May 2021 05:30:59 GMT

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.

This is what I want

Asset Serial AssetCountFlag

ASD5679 0
ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
GHJ8900 0
ERT6781 0

Re: Creating a sequence counter

tresB — Thu, 13 May 2021 05:46:49 GMT

Try this:

if([Asset Serial]= peek([Asset Serial]),1,0) as AssetCountFlag

Note: This would work only when your data is sorted on this field

Re: Creating a sequence counter

mato32188 — Thu, 13 May 2021 06:58:35 GMT

Hi @seraphis ,

tmp:

load

From xxx;

noconcatenate tmp2: load * resident tmp order by "Asset Serial"; drop table tmp;

tmp3:

Load *,

"Asset Serial" = Previous("Asset Serial") * (-1) as AssetCountFlag

resident tmp2;

drop table tmp2;

Re: Creating a sequence counter

seraphis — Thu, 13 May 2021 08:04:05 GMT

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.

What I currently have

Table Case1

Asset Serial AssetCountFlag

ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
GHJ8900 0
ERT6781 0
WER8913 1
WER8913 1

What I want in Table Case2

Asset Serial AssetCountFlag

ASD5679 0
ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
GHJ8900 0
ERT6781 0
WER8913 0
WER8913 1

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;

Re: Creating a sequence counter

seraphis — Thu, 13 May 2021 08:17:02 GMT

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.

What I have in Table Case1

AssetSerial AssetCountFlag

ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
GHJ8900 0
ERT6781 0
WER9045 1
WER9045 1

What I want in Table Case2

AssetSerial AssetCountFlag

ASD5679 0
ASD5679 1
ASD5679 1
ASD5679 1
ASD5679 1
GHJ8900 0
ERT6781 0
WER9045 0
WER9045 1

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?