topic Re: explination? in QlikView

explination?

Tue, 28 Oct 2014 15:02:21 GMT

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?

Tue, 28 Oct 2014 15:13:07 GMT

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 — Tue, 28 Oct 2014 15:15:38 GMT

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

Tue, 28 Oct 2014 15:17:42 GMT

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

Tue, 28 Oct 2014 15:24:43 GMT

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 — Tue, 28 Oct 2014 15:36:02 GMT

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