topic Re: Alternate states with a common column in QlikView

Alternate states with a common column

Anonymous — Fri, 10 Mar 2017 21:01:27 GMT

I have 3 tables Products ,Customer and shops

Shops table is the connecting Products and Customers in data model.I am displaying Products details in one sheet and Customer details in other sheet with alternate states enabled but I want Shop_Name column(Used as filter in both sheets) from shop table to effect both the sheets commonly .How to implement this

Re: Alternate states with a common column

Anonymous — Sat, 11 Mar 2017 09:08:04 GMT

build a listbox for [Shop_Name column] in "$-State" and change your expression(s) in the following way

sum( { [YOUR_ALTERNATE_STATE] <[Shop_Name column]=$::[Shop_Name column]>} yourvalue)

Then all fields besides [Shop_Name column] are in [YOUR_ALTERNATE_STATE]

Re: Alternate states with a common column

Anonymous — Mon, 13 Mar 2017 20:00:47 GMT

I am using Shop_Name Column on both the sheets,Using your logic,if Shop_Name filter is selected on one sheet ,other sheet Shop_Name filter is just hiding non matching rows.

Re: Alternate states with a common column

CarlosAMonroy — Mon, 13 Mar 2017 20:13:11 GMT

Hi Krishna,

If you can share a sample file, that would be great. For me make sense what Robin suggested you but it's not for you.

Thanks,

Carlos

Re: Alternate states with a common column

Anonymous — Mon, 13 Mar 2017 20:14:48 GMT

please prepare a small example...

Re: Alternate states with a common column

Anonymous — Mon, 13 Mar 2017 21:01:13 GMT

Please find the sample file attached.I want the shop_Name column to work commonly on both the sheets

Re: Alternate states with a common column

tresB — Tue, 14 Mar 2017 04:40:39 GMT

In the Product sheet use state <default state> for Shop Name field both in table and listbox.

Re: Alternate states with a common column

CarlosAMonroy — Tue, 14 Mar 2017 12:22:49 GMT

Hi Krishna,

I'm not sure if I completely understand your request. Please help me understand what you need.

Alternate states performs comparative analysis between sets of multiple data items.

Now what is your approach, if you leave both sheets in the default state and you select any values from the shop filter, you'll see both sheets are affected by the selections you made.

The next link may be helpful. How to use Visual Cues and Alternate States in QlikView?

Thanks,

Carlos

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 14:49:53 GMT

Hi Carlos,

I am trying to use Alternate states in little Hacky way .I just want two alternate states objects to respond for a single common filter.In my case that filter is Shop name and I want to place that filter on both the sheets.Apart from Shop name filter ,two sheets have their own filters .Those filters should affect only their own state objects

I can achieve this with set analysis but I want to try with alternate states

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 14:56:35 GMT

Hi Tresesco,

Thanks for your response but It will not fit my requirement.If I use your logic,Location filter on product sheet is not affecting other objects on product sheet and I also don't want the location filter on customer sheet to affect the product sheet objects

Re: Alternate states with a common column

CarlosAMonroy — Tue, 14 Mar 2017 15:15:41 GMT

Ok, that's fair.

So, what comes to my mind is the following:

Go to Settings -> Document Properties, under Triggers tab, section Field Event Triggers, add an 'OnSelect' action.

The action should be a selection type:

Select in field = Shop_Name and Searching String = =Shop_Name.

Then create another action with the same parameters but the trick is in the Alternate State selection, by choosing Product.

Here is the Screenshot.

Now when you select a shop, will be selected on both sheets.

Thanks,

Carlos

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 15:51:47 GMT

Super,It works.

The only problem I see is when I select multiple Shop_names from the filter.It is not taking any selections.I tried to use ='('&Concat(Distinct Shop_Name,'|')&')' in search string but didn't work properly

Re: Alternate states with a common column

CarlosAMonroy — Tue, 14 Mar 2017 16:44:03 GMT

That works perfect for me. Check again the syntax and remember to use the same expression in both actions.

Thanks,

Carlos M

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 16:46:09 GMT

Just set the shopnames with" like that

("Shop1"|"Shop 2")

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 17:07:37 GMT

='("'&Concat(DISTINCT [Shop_Name],'"|"')&'")'

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 17:32:23 GMT

='("'&Concat({[YOUR_ALTERNATE_STATE]} DISTINCT [Shop_Name],'"|"')&'")'

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 19:14:25 GMT

Carlos/Robin

It is working fine but if there is a space in Shop Name field value,it is not picking up.Please find the attached sample.I added a shop name 'Text 1' and I am unable to select that field value

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 19:15:35 GMT

have a look at my answers below...

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 19:33:26 GMT

I did tried the option '("'&Concat(DISTINCT [Shop_Name],'"|"')&'")'

But when noting is selected it is by default selecting all field values in Shop_Name filter

Re: Alternate states with a common column

Anonymous — Tue, 14 Mar 2017 19:44:00 GMT

if(getselectedcount(Shop_Name)>0,

'("'&Concat(DISTINCT [Shop_Name],'"|"')&'")'

)