Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
vignesh_s
Contributor

hi,have doute in pivot table(Enable condition)in dimension tab

Dimension:

LOAD * INLINE [

    _Dimention

    ProductName

    ProductID

    Office

    OfficeCity

    OfficePhone

    OfficeCountry

];

and i have a productName in Product Table too,i created a pivot table with dimentions as ProductNAme ,Office etc and given expression as below,

=SubStringCount(Concat(_Dimention,'|'),'ProductName')

can any expalin how that expression working

1 Solution

Accepted Solutions
Highlighted
omkarvamsi
Contributor

Re: hi,have doute in pivot table(Enable condition)in dimension tab

i guess you are asking about adhoc reporting.

=SubStringCount(Concat(_Dimention,'|'),'ProductName')


concat will create a string with delimter '|'.

for example: A|B|C|D|E|F|B


substring will return count of the word/letter you mentioned in substring count

in mentioned example if we write as substringcount('A|B|C|D|E|F|B','B')

the above will return 2 as B is repeating two times.



in your example the substringcount(concat()) will retune 1 as ProductName is repeated once in the _Dimension.

as it returns one,the conditon enable and become true.


so when even you click on _Dimension the count shows 1 and condition will enable and chart will display respective selection.


Hope it helps you.


Regards

Om

View solution in original post

4 Replies
Highlighted

Re: hi,have doute in pivot table(Enable condition)in dimension tab

Let's talk with inline

_Dimension is the field which contains A, B, C, D ??

So, Concat(_Dimension, '|') -- Will return A|B|C|D

From here, We are going to use

SubStringCount(concat(_Dimension, '|'), 'C') -- Which contains one as Count

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
omkarvamsi
Contributor

Re: hi,have doute in pivot table(Enable condition)in dimension tab

i guess you are asking about adhoc reporting.

=SubStringCount(Concat(_Dimention,'|'),'ProductName')


concat will create a string with delimter '|'.

for example: A|B|C|D|E|F|B


substring will return count of the word/letter you mentioned in substring count

in mentioned example if we write as substringcount('A|B|C|D|E|F|B','B')

the above will return 2 as B is repeating two times.



in your example the substringcount(concat()) will retune 1 as ProductName is repeated once in the _Dimension.

as it returns one,the conditon enable and become true.


so when even you click on _Dimension the count shows 1 and condition will enable and chart will display respective selection.


Hope it helps you.


Regards

Om

View solution in original post

Highlighted
vignesh_s
Contributor

Re: hi,have doute in pivot table(Enable condition)in dimension tab

Thank you so much ,it held me a lot

Highlighted
omkarvamsi
Contributor

Re: hi,have doute in pivot table(Enable condition)in dimension tab

you are welcome Vignesh