topic Re: Seperate and count the delimited values using front end expression in QlikView

Seperate and count the delimited values using front end expression

Tue, 28 Jul 2015 14:47:50 GMT

Hi Team,

Is it possible to get below given output from the input data using front end expression.

Re: Seperate and count the delimited values using front end expression

a_mullick — Wed, 29 Jul 2015 10:37:08 GMT

Hi,

I am not sure it is, as you need some sort of 'explode()' function.

Much easier (and more flexible) to do at script load...

Thanks,

Azam

Re: Seperate and count the delimited values using front end expression

sushil353 — Wed, 29 Jul 2015 10:41:45 GMT

Hi,

Try this:

SubStringCount(Concat(Dept_List),',')

HTH

Sushil

Re: Seperate and count the delimited values using front end expression

a_mullick — Wed, 29 Jul 2015 10:46:46 GMT

I thought that at first too, but I think it will fail as the example given needs a distinct count of departments.

Cheers,

Azam

Re: Seperate and count the delimited values using front end expression

sushil353 — Wed, 29 Jul 2015 10:50:03 GMT

PFA

It is working as expected

Re: Seperate and count the delimited values using front end expression

sunny_talwar — Wed, 29 Jul 2015 10:54:01 GMT

I just tested Sushil‌'s expression and it seems to be working well:

Re: Seperate and count the delimited values using front end expression

tresB — Wed, 29 Jul 2015 11:03:49 GMT

Sushil's expression actually counts the number of commas (',') which is incorrect(because in that distinction is not being considered). Try adding more rows with repeated department - you will see the wrong output.

I guess it's going to be a tricky one (if at all possible) in the front-end.

Re: Seperate and count the delimited values using front end expression

a_mullick — Wed, 29 Jul 2015 11:05:28 GMT

Ok, but I think this may be more good fortune - I'm not sure it will generalise to more complex cases, the concat() function results in an undefined dept 'BA' in the intermediate results and it is only correct for EMP2, because 'D' is repeated, if EMP2 were A,B,C,D,E, it would return the wrong result (4 insted of 5)...but yes it works for this exact example.

Thanks,

Azam

Re: Seperate and count the delimited values using front end expression

avinashelite — Wed, 29 Jul 2015 11:11:06 GMT

try to do it in the script level that would be the best to achieve it, front expression will not be sure on this kind of case

Re: Seperate and count the delimited values using front end expression

Wed, 29 Jul 2015 13:11:50 GMT

Hi Azam,

I want to do this in expression level. Not like the method used in attached file

Thanks,

Anoop

Re: Seperate and count the delimited values using front end expression

Wed, 29 Jul 2015 13:14:03 GMT

Hi Sushil,

Distinct values need to be counted not the comas. This is not working.

Thanks,

Anoop

Re: Seperate and count the delimited values using front end expression

Wed, 29 Jul 2015 13:20:27 GMT

Hi Sushil,

Since I need to count the distinct values from the coma separated value this method is not working,

it is failing in the below scenario.

[Step 1]:

LOAD * INLINE [

Emp_Name, Dept_List

EMP1, "A, B, C"

EMP1, "A, B"

EMP2, "D, C, A, D, E,E,E,E"

];

Its just counting the comas in the string.

Thanks,

Anoop

Re: Seperate and count the delimited values using front end expression

Wed, 29 Jul 2015 13:25:28 GMT

Tresesco,

Exactly, its going to be a bit tricky to be solved using front end expression.

Thnaks,

Anoop

Re: Seperate and count the delimited values using front end expression

Wed, 29 Jul 2015 13:32:35 GMT

Hi Avinash,

If I have to handle this in script level, I will be ending up adding a big table, only for accommodating these split values.

It will be fine for a smaller data set where as my application is dealing around 15 million records.

Currently I have solved the issue using this method only but need to check a better way to optimize the performance.

Thanks,

Anoop