Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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().
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 ~$).
how about
substringcount(([star excel],'\')+1),2)
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.
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]))