topic Re: SubStringCount(Concat in QlikView

SubStringCount(Concat

hjm35055 — Fri, 28 Sep 2018 17:25:37 GMT

Hi,

I have the following inline tables in my app which are used to conditionally show dimensions in a table.

In the one table I am using the following formula and everything works fine.

=SubStringCount(Concat(%dimensionID, '|'), 'A')

In the other table I am using this formula and nothing is working.

=SubStringCount(Concat(%refresh_dimensionID, '|'), 'A')

Can anyone please tell me why? I am not referencing the same fields in both tables, for example City in %dimension is "Physical City" and City in %refresh_dimension is "Customer City". I tried changing the ID from letters to numbers in %refresh_dimension but it still didn't work.

Dimensions:

LOAD * INLINE [

%dimension, %dimensionID

City, A

State, B

Profit Center, C

Profit Center Name, R

Location, D

Date, E

Day Part, F

Time, G

Menu Item Name, H

Major Group, I

Family Group, J

Tender, K

Weather, L

Temperature, M

Check#, N

Week, O

Month, P

Day, Q

];

Refresh_Dimensions:

LOAD * INLINE [

%refresh_dimension, %refresh_dimensionID

Market Center, A

Customer, B

Quarter, C

State, D

City, E

Address, F

Year, G

Customer#, H

Customer Contact, I

Invoice#, J

Ticket Date, K

Product Code, L

Product Desc, M

Category, N

UOM, O

Qty Per, P

Tax, Q

Month-Year, R

Rank Sales, S

Rank Units, T

];

Re: SubStringCount(Concat

vishsaggi — Fri, 28 Sep 2018 17:38:42 GMT

rename your %refresh_dimensionID to just %dimensionID and check? IF not can you share an eg of your expected output with some sample data?

Re: SubStringCount(Concat

hjm35055 — Fri, 28 Sep 2018 18:26:40 GMT

Here you go!

Re: SubStringCount(Concat

vishsaggi — Fri, 28 Sep 2018 18:49:01 GMT

Your Calculation condition in your chart properties general tab is pointing to %dimension, it should be %refresh_dimension as shown below change it and also change your substringcount() function to refresh_dimensionID like A, B, C. There are no D, E,F in your refresh_dimensionID field.