Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

explination?

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?

5 Replies
Not applicable
Author

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 ~$).

Not applicable
Author

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

Not applicable
Author

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]))