topic Re: Dynamically adding columns to the Straight table. in QlikView

Dynamically adding columns to the Straight table.

kalyanamharish — Tue, 30 Oct 2018 15:30:02 GMT

I have 50 columns in a table. I need any 10 columns to be displayed as default because having entire 50 columns would make the appearance messy , as it will lead to a lot of scrolling towards right in the straight table. So any 10 columns as default initially and options like expanding or list box to add other 40 columns to the table when required.

My efforts so far

I created a inline table of the same original table and placed this inline table in a list box with only 40 Fields.

then I created a straight table with 9 Dimensions and 1 Date measure which are not there in list box. Now I am stuck what should i write the enable condition for Dimension.

or is there any all together a different work around for this.

Re: Dynamically adding columns to the Straight table.

vitaliichupryna — Tue, 30 Oct 2018 15:39:56 GMT

Hi Harish,

Please check following link:

Adhoc reporting in Qlikview

Thanks,

Vitalii

Re: Dynamically adding columns to the Straight table.

kalyanamharish — Tue, 30 Oct 2018 16:02:39 GMT

I have gone through that article @Vitalii Chuprina, Could you please explain these two conditions.

=SubStringCount (Concat (PARTICIPANT_DIMENSION, '|'), 'FIRST_NAME')

=SubStringCount (Concat (PARTICIPANT_MEASURE, '|'), 1)

Re: Dynamically adding columns to the Straight table.

vitaliichupryna — Tue, 30 Oct 2018 16:22:52 GMT

Concat (PARTICIPANT_DIMENSION, '|') and Concat (PARTICIPANT_MEASURE, '|')

Those functions create string with dimensions and measures.

For example if you have column

PARTICIPANT_DIMENSION

FIRST_NAME,

SECOND_NAME,

STREET

Concat (PARTICIPANT_DIMENSION, '|') will create the following string FIRST_NAME|SECOND_NAME|STREET|

SubStringCount function returns number of substrings in the string, so you in case of

SubStringCount (Concat (PARTICIPANT_DIMENSION, '|'), 'FIRST_NAME') it will return 1.

When you select FIRST_NAME in PARTICIPANT_DIMENSION substringcount returns 1 - "1" is true, so you will see your FIRST_NAME as dimension.

The same logic works with expressions, but I would recommend you to use alphabet letters instead of numbers if you have more than ten measures.

SubStringCount (Concat (PARTICIPANT_MEASURE, '|'), A)

Because for measures you will have following string 1|2|3|4|5|6|7|8|9|10| and if you select first measure in your measure list, you will see the 1 and 10 measure.

Thanks,

Vitalii

Re: Dynamically adding columns to the Straight table.

vitaliichupryna — Tue, 30 Oct 2018 16:31:13 GMT

The better solution for dimensions is following:

Create list with dimension and id of dimension

Load * Inline [

Dimension, DimNumber

Client, 1

City, 2

...

Division, 50]

After this for dimension calculation use for the first dimension:

=$(='['&FirstSortedValue([Dimension], [DimNumber], 1)&']')

For Dimension label:

=FirstSortedValue([Dimension], [DimNumber], 1)

Condition:

GetSelectedCount([Dimension])>=1

For the second dimension:

=$(='['&FirstSortedValue([Dimension], [DimNumber], 2)&']')

For Dimension label:

=FirstSortedValue([Dimension], [DimNumber], 2)

Condition:

GetSelectedCount([Dimension])>=2

This is flexible solution, so you can create only 10 calculated dimensions in the chart, but you will be able to show every dimension in you list.

Thanks,

Vitalii

Re: Dynamically adding columns to the Straight table.

kalyanamharish — Tue, 30 Oct 2018 16:42:16 GMT

Thanks for great explanation.but i am a bit confused.

the qvw which you ask me a refer

Under expression >>

Measure = Sales

Conditional = =SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Sum|')

Label = sale

Definition = =Sum(Sales)

but in my case i have for instance ADDRESS1 and HIREDATE as measure

Measure = ADDRESS1

Definition = ADDRESS1

then what should be my Condition in this case?

I am confused with the word SUM in adhoc qvw >> Expression >> Condition , because i do not have any such need in my case.

Re: Dynamically adding columns to the Straight table.

Jesh19 — Tue, 30 Oct 2018 16:51:01 GMT

Hi Harish,

=SubStringCount (Concat (PARTICIPANT_DIMENSION, '|'), 'FIRST_NAME')

This concatenates all the values in PARTICIPANT_DIMENSION

If your PARTICIPANT_DIMENSION contains values like FIRST_NAME, LAST_NAME, MIDDLE_NAME, AGE, CITY

SubStringCount (Concat (PARTICIPANT_DIMENSION, '|'), 'FIRST_NAME') gives you 1 for conditional check for dimension FIRST_NAME.

Similarly for remaining dimensions.

The same is applicable to Measures as well.

Hope this helps.

Regards,

Jesh

Re: Dynamically adding columns to the Straight table.

Jesh19 — Tue, 30 Oct 2018 16:53:51 GMT

=SubStringCount (Concat (MEASURE, '|'), 'ADDRESS1')

This should be condition for Address1 Measure

Re: Dynamically adding columns to the Straight table.

kalyanamharish — Thu, 01 Nov 2018 08:37:06 GMT

Hello vitaliichupryna‌ Do you want me to add this as a calculated dimension?

=$(='['&FirstSortedValue([Dimension], [DimNumber], 2)&']')

Re: Dynamically adding columns to the Straight table.

vitaliichupryna — Thu, 01 Nov 2018 08:54:35 GMT

Hi Harish,

Yes, it's possible solution. If you use it you will have really dynamical table.

If you use solution in document you should have all dimensions in the table

In case of =$(='['&FirstSortedValue([Dimension], [DimNumber], 2)&']')

You can create for example 20 calculated dimensions, and your customer will be able select any 20 dimensions from dimension list.

Calculated Dimension:

=$(='['&FirstSortedValue([Dimension], [DimNumber], 2)&']')

Dimension label:

=FirstSortedValue([Dimension], [DimNumber], 2)

Condition:

GetSelectedCount([Dimension])>=2

Thanks,

Vitalii

Re: Dynamically adding columns to the Straight table.

vitaliichupryna — Thu, 01 Nov 2018 09:34:41 GMT

Harish,

Check attached file, in Dimension list I have four dimensions but in dynamical table only 3 calculated dimension.

Thanks,

Vitalii

Re: Dynamically adding columns to the Straight table.

kalyanamharish — Fri, 02 Nov 2018 09:03:48 GMT

Thanks you very much vitaliichupryna‌ for this Example.qvw . Now I got a clear picture, i will try to rebuilt my doc in this way.

Regard

Harish