topic Re: Overrule State in Concat in QlikView

Overrule State in Concat

amien — Thu, 10 Sep 2015 08:38:31 GMT

Is it possible to overrule the state in the Concate function?

=concat({State1} distinct selTable1,',')

I tried with "*" but doesn't work.

I'm using this function to create customer reporting. Where the end-user can select fields in a listbox and the above syntax is used for the condition in the straight tabel.

The state i'm using because i have 6 listboxes, for each tabel 1. It would be too confusing to put all the fields in one listbox. I need the alternatate state to prevent the deselection of fields. Detaching the listbox doesn't work / no option for it.

Thanks in advanced

Re: Overrule State in Concat

amien — Thu, 10 Sep 2015 09:03:09 GMT

=concat({State1+State2} distinct selTable,',')

This would work. But i have quite some States and i won't want to put them all in the expressions

Re: Overrule State in Concat

jonathandienst — Thu, 10 Sep 2015 09:07:52 GMT

Why do you want to union all the states? Personally, I have never used more than 2 alternate states...

What are you trying to do? Perhaps alternates states is not the best solution.

Re: Overrule State in Concat

sunny_talwar — Thu, 10 Sep 2015 09:14:07 GMT

Not entirely sure what you are looking for, but may be this:

=Concat({1} Distinct selTable1,',')

Re: Overrule State in Concat

amien — Thu, 10 Sep 2015 09:23:53 GMT

i'm trying to create a customer report functionally for the end-users. end-users can select fields in a listbox. And based on this selection those specific dimensions will be shown in the straight table. Like this url:

http://www.analyticsvidhya.com/blog/2014/01/customize-reporting-qlikview/

This solution is very hard to maintain if you have a LOT of dimensions (like 500).

So my goal is not to put all the individual dimensions in the straight table. But only the first selected 100 or so. This would be more then sufficient.

i can get this using this : subfield(concat(distinct selTable,','),',',1)

I can copy this dimension like 100 times. last one will be subfield(concat(distinct selTable,','),',',100)

Now the end user can select max of 100 fields in the listbox and create a custom report. regardsless which fields they select

Now comes the additional challange:

Because there are alot of fields i need to structure it. So i created like 5 listboxes based on the same selection table. The listboxes contain a calculted dimension : If(Label = 'Customer',selTable). Other listbox will be like : If(Label = 'OrderDetails',selTable)

If i would add this, it will not be possible for the end-user to select both some fields out of Customer and out of OrderDetails at the same time. I'm using Alternate state of overrule this (State1 = Customer, State2= OrderDetails). But if i would do this, it will effect the subfield(concat(distinct selTable,','),',',1) statement in the straight-table. To fix this, i need to overrule the alternate state in this place : concat({State1+State2} distinct selTable,',')

Re: Overrule State in Concat

amien — Thu, 10 Sep 2015 09:44:41 GMT

Doesn't work Sunny,

I added an example.

adding this to the dimension condition is also an issue : GetSelectedCount(selTable) > 0

Re: Overrule State in Concat

sunny_talwar — Thu, 10 Sep 2015 09:47:53 GMT

Working with PE of QlikView, but I am sure someone else will def. be able to help you out.

Best,

Sunny

Re: Overrule State in Concat

amien — Thu, 10 Sep 2015 11:11:43 GMT

It tried this to do without Alternate States:

=$(=('[' & SubField(concat(distinct Dims1,'|') & '|' & concat(distinct Dims2,'|'),'|',1)) & ']')

This works, only 1 issue:

What do i use on the conditional?

=GetSelectedCount(Dims1) > 0 OR GetSelectedCount(Dims2) > 0

is not working : When i select nothing in Dims1 and i only 1 field in Dims2, it will show also values from Dims1

Re: Overrule State in Concat

amien — Thu, 10 Sep 2015 11:32:17 GMT

Think this is working:

Dimension:

=$(=('[' & SubField(concat(distinct Dims1,'|') & '|' & concat(distinct Dims2,'|'),'|',1)) & ']')

Condition:

Len(SubField(GetFieldSelections(Dims1,'|') & '|' & GetFieldSelections(Dims2,'|'),'|',1))