topic Re: Using Sheets for different views in QV in QlikView

Using Sheets for different views in QV

Tue, 18 Jun 2013 16:36:57 GMT

Good morning all,

I added several scheets to my QV doc. I them added similar table objects to each sheet and set a multi box for each so I could filter each a different way but when I apply a filter on one sheet, it filters all the sheets the same way. When I go to another sheet and hit that filter, it changes all the others to match again.

No I have not copy/pasted anything. I added all the sheets manually and named them separately. Smae for each of the table boxes and multi boxes. I created them all manually and named them differently.

So, why do all the sheets change to match whichever one I use the filter on and how do I prevent that?

Steve

Re: Using Sheets for different views in QV

Alexander_Thor — Tue, 18 Jun 2013 16:59:41 GMT

So what you are experiencing is core functionality in QV. When you are selecting something you are actually "filtering" the underlaying data model which in turn affects all your objects and sheets and re-calculates your expressions.

In theory you could most likely reduce your application to only one sheet with a few tables and a few list/multi boxes to filter your data.

IF you still want to work around the core functionality there is a couple of approaches:

Add Set analysis to every expression to disregard certain selections.
Set every sheet to it's own Alternate State which will create a incapsulated state for every sheet/object/expression that share that state.

Do enable this you need QV11+. Go to Document Properties -> General Tab. Click Alternate State. Add the amount of states that you need. Now for every Sheet you could go to Sheet Properties -> General Tab. Set the sheet to your desired state. Every object and expression on that sheet will inherit your state.

Repeat the process for every sheet/object/expression that you want to seperate into a different state.
That would create the behaviour you are looking for - or use excel

Re: Using Sheets for different views in QV

Tue, 18 Jun 2013 18:30:27 GMT

Thanks! That is very helpful information. I will review the info you gave me and see what I can do, but maybe it would be helpful if I provide more detail as to what i am trying to do.

I exported a users Outlook Calendar to Excel and imported it to QV, removing a lot of uneeded detail in the process. This became the basis of a training calendar for the user where thay could view training schedules for new-hires

The remaining desired columns I displayed in a table box...so far, so good.

The user wanted to be able to filter the list by trainee. The problem is that the trainees exist in the "Required Attendees" column along with the trainrs and other attendees.

I created a multi box with an expression for each of the trainiees

=If ((RequiredAttendees LIKE '*trainee name*'),1,0)

Again, so far, so good. The multi box has an expression like the above for each of the trainees. The user can selct the drop down for a given trainee and click the "1" and the table box will display all the training session data for that trainee.

But...the user wants to sort the table box by the Trainee. Sinnce the column has all the trainees in it with many (though not all) in the same sessions, that is not an easy thing to to. In order for a full and separate record for each trainee to be created, we would have to have many separate copies of each session.

That is where I came up with the bright idea of separate table boxes. I had hoped (incorrectly I now know) that If I created sepoarate pages with separate table boxes and separate multi boxes (one for each trainee) I had surmised that I could cause each table box to dispay the sessions for only one of the trainees. I further surmised that I could then copy all of these multi boxes to a single page, each labeled for their respected trainee, that I could, in that fashion, display separate lists of training session for each trainee.

Obviously, i was wrong

So, is there some other way to beable to sort by this column as the user requests?

As I mentioned, with the doc as it is currently designed, the user can use the multi box to select each trainee one at a time to see thiie schedule. But the user wants to see all of their schedules at once, either all in one list or as separate lists on the same page.

Is there any reasonable way to do this?

Steve

Re: Using Sheets for different views in QV

Alexander_Thor — Tue, 18 Jun 2013 18:52:57 GMT

Hey Steve,

With a little bit of data modeling it sounds very doable. If you can upload a sample app I can mock-up a solution for you. If you need to scramble data you can do that under document properties -> fields

Re: Using Sheets for different views in QV

Tue, 18 Jun 2013 18:58:24 GMT

No worries, here you go...

Re: Using Sheets for different views in QV

Alexander_Thor — Tue, 18 Jun 2013 20:26:24 GMT

Ok... So I probably went a little bit overboard...

Anyways this would let your users easily filter between different attendees to see their training history.

With the green - white - grey concept you can now also select a module and easily see who have not attended that training so far.

If you open up the script I have commented the different sections so you should easily be able to apply it to your dataset.

Edit: Completly forgot - Now I extract every attendee to the course since I don't know who's a trainee or not. If you want to map that you could easily create a inline table with the trainee and maybe a Yes flag or something.

LOAD * INLINE [
    RequiredAttendees, Is Trainee?
    Jennifer Perkins, Yes
    Some Other Name, Yes
];

This would link to your attendee field with the attendee name as a key. You would then get a field where they could select 'Yes' and only see trainees.

This is the simplest approach - if you want to go more advanced read up on the applymap function either on the community or in the help file.

Re: Using Sheets for different views in QV

Tue, 18 Jun 2013 20:38:12 GMT

This looks like exactly what my user is looking for. I gotta say it will take me a while to digest this as it is beyond my abilities right now but it is very cool.

I will break it down till I understand the components and can manipulate it and reporduce it.

Thaks a million for all the help.

Steve