Problem with formula

[huytonscouser]

I am trying to generate a unique checksum for the data field
{Cluster_Data_for_Export___Phoenix.Field14}.

The majority of the values returned are unique, and the formuala truly returns a duplicated value when {Cluster_Data_for_Export___Phoenix.Field14} is the same.

i.e

PHXTSS 1,196.00
PHXTSS 1,196.00
PHXTSS 1,196.00
PHXTSS 1,196.00


However the formula also returns duplicate values for
{Cluster_Data_for_Export___Phoenix.Field14} when it is different.


i.e

WPDWD710 1,206.00
WPDWD503 1,206.00
WPDWD503 1,206.00
WGPWA503 1,206.00
WGPWA512 1,206.00
WGQWA502 1,206.00
WGQWA511 1,206.00

the formula is :-

numbervar looper := length(GroupName ({Cluster_Data_for_Export___Phoenix.Field14}));
numbervar checksum :=0;
numbervar i:=1;
stringvar array charoffset := makearray(
"T","U","V","W","X","Y","Z","[","\","]","^","_",
"'","a","b","c","d","e","f","g","h","i","j","k",
"l","m","n","o","p","q","r","s","t","u","v","w",
"x","y","z","(","|",")",",","-",
"0","1","2","3","4","5","6","7","8","9",":",";",
"<","=",">","?","@","A","B","C","D","E","F","G",
"H","I","J","K","L","M","N","O","P","Q","R","S");


while i <= looper do
(

checksum :=checksum + ascw(Mid(GroupName ({Cluster_Data_for_Export___Phoenix.Field14}),i,1));
numbervar thischar := ascw(Mid(GroupName ({Cluster_Data_for_Export___Phoenix.Field14}),i,1)) - 47;
numbervar unique := ascw(charoffset[thischar]);
checksum := checksum+unique;
i := i+1;
);
checksum ;


For each character in ({Cluster_Data_for_Export___Phoenix.Field14}
its value is gained via ascw, then to make it unique i use the ascw value as an offset into a character array. The -47 is so that a correct offset value is gained,as my array only contains 0-9, a-z and A-Z, and some special characters ?()[]....etc

can anyone see what is wrong with the formula ?

Thanks.

 

[lbass]

I checked this out and the problem is that your formula does not create unique values. If you parse out the value of each letter in the code, you will see that these codes return the same value.

What is your purpose in replacing values that are already unique? In other words, why are you trying to convert the string to a number? You could instead replace each letter with numeric text and return a string that you could then convert to a number, e.g., using the acsw values directly with no offset:

WGPWA503 8771808765534851

-LB

 

[huytonscouser]

Thank you, and maybe i am over analyzing the problem. It reverst back to an earlier thread i created, about best methid to compare to .xls worksheets. TODAY and YESTERDAY, so i can report on what changed.

Both worksheets are exactly the same, and today is just renamed yesterday at just before before midnight, before the next day generates the current data(today).

i am trying to create a key for the columns i know can change, and then use that generate the links. As you are aware, ,xls only seems to support inner join and left outer.

if i try and prepare a report using today.column1 > yesterday.column1 i only get the matches, and column1 contains many of the same values. So i wanted to generate a checksum for column1 + a checksum for another column, and then do any links.

So whats the best way to prepare a detailed report for two .xls worksheets ? i can see 3rd party software to do it (Florencesoft™ DiffEngineX™ Compares Microsoft® Excel Worksheets)

sorry for sounding so confusing, and thanjs for your help.