explination?

[#1]

count(distinct(if(left(subfield([star excel],'\',substringcount(([star excel],'\')+1),2) <>'~$' and [x1flg]='n',[star excel]))

how it works pls explain me step by step?

[#2]

Hi,

Finally here if(subfield([star excel]<>'~$' and [x1flg]='n' then count(distinct[star excel])

here in the [star excel] we have data starts with '~$' so they used left and using of substringcount().

[rubenmarin]

Seems it counts the distinct [start excel] where the value after the last '\' doesn't begins with '~$'.

In other words I think is to count the excel files wich are not temporary (filaname begins with ~$).

[#4]

how about
substringcount(([star excel],'\')+1),2)

[#5]

Hi,

subfield([star excel],'\',substringcount(([star excel],'\')+1),2)

substringcount([star excel],'\')+1 ---> it will count how many '\' are there  suppose if we have 5 then it will add +1 then finally it is 6.

now it is subfield([star excel],'\',6,2)

here we have left(subfield([star excel],'\',6,2)  ----> here one more ')' is missed before 2.

[rubenmarin]

substringcount(([star excel],'\')+1) counts the '\' values

it adds +1 for the subfield function to return the text after the last '\' it founds

The last ',2)' it's for the left expression, to return the first 2 characters of the string returned by the subfield function,

I think there is and extra '(' after substringcount, also after the 'distinct' so it should be:

count(distinct if(left(subfield([star excel],'\',substringcount([star excel],'\')+1),2) <>'~$' and [x1flg]='n',[star excel]))