topic Create table based on Fields selected in List Box in QlikView

Create table based on Fields selected in List Box

Anonymous — Thu, 03 Jun 2010 16:10:44 GMT

Hello all,

I swear that I had seen this as a post sometime ago, but I could not find it with Search (my lack of being able to come up with a key word, I suppose). Does anyone have any macros that will allow a user to make field selections in a list box and then have a table created with those selected field names? I am looking to get rid of the constant requirement of calls to add a certain field to a report. We use the plugin, so it must be able to work in both the developer and plugin environment.

Thanks,

Create table based on Fields selected in List Box

Thu, 03 Jun 2010 16:15:28 GMT

do you have this one in mind?

Regards

Juerg

Create table based on Fields selected in List Box

Anonymous — Thu, 03 Jun 2010 16:35:54 GMT

This is definitely traveling down the path that I want to go. This will give me a good start.

In your document, you talk about it crashing the system with more than one person attempting the dynamic reporting, does this happen often? My thought pattern was to create a table on the fly using the create "CreateTableBox" macro function and assign the fields based on selection in a list box (no aggregation). Would this cause me an issue if two users tried to create tables at the same time using the same Table ID?

Thanks for you help here, saves me alot of testing!

Create table based on Fields selected in List Box

johnw — Thu, 03 Jun 2010 18:45:55 GMT

I built the template, so I'll try to answer, and attached is my most recent version. No, the crash doesn't happen often. In fact, I haven't seen it in a couple years, I think. However, very few users use this functionality, so that probably doesn't prove anything.

I've tried creating the table on the fly. It was a COMPLETE PAIN and very, very slow. Unacceptably slow. Still, give it a shot. Maybe it will work for you. It DOES give you a lot of control. It's just a whole, whole lot of macro code. And unless you're giving the table an ID yourself, I believe that two different users would have two different table IDs, so there wouldn't be conflicts to crash it.

Another approach we've been looking into, but somehow never found the time to really do it, is to CLONE a hidden table and then remove all of the dimensions and expressions you don't need. Since each user would then have a different table ID, there wouldn't be conflicts to crash it.

Another approach we've been looking into involves the ability in version 9 to conditionally show columns. I'll attach that example as well. Unfortunately for our purposes, data is still broken down by hidden dimensions, so I think we'd still need a macro to manage dimensions, which still exposes us to all the problems of macro use, so it doesn't seem worth "upgrading" our actual applications.

If you only have a few dimensions to choose from, it might be practical to have variables with drop down lists of possible dimensions. Then if you have, say, three possible dimensions, have separate charts for one, two and three dimensions selected. That would probably combine well with conditional show of expressions to create a macro-free version of this, which would be desirable due to the drawbacks of macros.

Create table based on Fields selected in List Box

johnw — Thu, 03 Jun 2010 18:47:30 GMT

Here's the version using conditional show of columns. It requires version 9, though.

Create table based on Fields selected in List Box

Anonymous — Thu, 03 Jun 2010 19:04:08 GMT

Thanks John! I had already found out creating a table was kinda slow via macro. It can do 1 - 5 columns no problem, but appears to slow down after that. I will take a look at your code and let you know if I have any other questions.

Thanks for the response and your time. It is greatly appreciated!!!

Create table based on Fields selected in List Box

Fri, 12 Nov 2010 15:57:37 GMT

Hi John... The dynamic table looks great!

Did you have any luck getting around this??

"This works fairly well for expressions, but even when a dimension is hidden, the data is still broken down by it."

Thansks, Mike

Create table based on Fields selected in List Box

johnw — Fri, 12 Nov 2010 18:04:33 GMT

I haven't attempted to do so, and honestly, I suspect it just means that approach won't work. It appears intentional that the data is still broken down by those columns. The columns are merely hidden and not disabled. To get what I need, I'd need a "conditional enable" for the dimensions. I'm not really expecting them to add that feature any time soon. I suppose I could at least suggest it on the customer portal, since it seems like a much cleaner way of handling dynamic reporting (for straight tables at least), and since dynamic reporting comes up frequently.

Looks like there's already a suggestion for a conditional enable of expressions. This is similar, but for dimensions. I think I'll add it as a new request instead of just commenting on the expressions request, but make reference to the expressions request in mine. OK, I added a new request:

https://emea.salesforce.com/ideas/viewIdea.apexp?id=08720000000HMz1

Conditional Enable of Dimensions

I would like to see an "enable" property added to dimensions, as it is for expressions, and for this property to allow a condition (perhaps a radio button with enable, disable and conditional). This perhaps could be merged with the idea "condition enable status of expression".

https://emea.salesforce.com/ideas/viewIdea.apexp?id=087200000008SuY&srPos=5&srKp=087

And it is similar to the requests (now satisfied) to be able to conditionally hide expressions and dimensions. But I thought I should propose it separately since it IS a different request, even if very similar. In addition to conditionally enabling expressions (which at least appears to be handled now by conditionally hiding them), I would like to be able to conditionally enable dimensions.

The "hide" functionality is not what I'm talking about. First, the hide isn't available on some types of charts where I'd want to use this, such as pivot tables, though obviously that could be addressed without a conditional enable. But second, when you hide a dimension, your chart is still broken down by that dimension. The dimension isn't displayed, but it is still being used. That seems like the correct functionality for "hide", but is not what I want. I want to be able to conditionally disable the dimension completely, making it as if that dimension were no longer part of the chart.

Having a conditional enable for dimensions combined with the conditional hide (or enable) of expressions would allow a clean solution to a long-standing problem, an often-requested type of chart. People want to be able to build dynamic charts where the USER can add and remove dimensions and expressions, such as by selecting fields from a list. I've implemented this in several live applications using a macro, and posted this solution to the forum on a number of occasions. However, it's a big macro with traditional macro weaknesses, and has been prone to crashing over the years. If instead every dimension and expression could just detect if it was selected, like with sum(Dimension='Customer') as the condition, then the macro could go away, and the charts should appear faster and be much more reliable.

A robust solution for a common forum request seems like a good idea to me.

Create table based on Fields selected in List Box

christian77 — Thu, 24 Mar 2011 12:56:01 GMT

Hi John:

Your code is much better than mine. And much simpler. That´s why you are top 10 member.

The thing is that neither mine nor yours work in QV 10.

What do we do?

Create table based on Fields selected in List Box

Thu, 24 Mar 2011 14:46:11 GMT

Could you use a bookmark to save the selections of a table? Then the user wouldn't have make any other selections.

Create table based on Fields selected in List Box

johnw — Thu, 24 Mar 2011 17:11:46 GMT


Christian77 wrote: The thing is that neither mine nor yours work in QV 10.
What do we do?

To make sure we're seeing the same problem, if any dimensions are selected, it comes up with the big X and the little button to try again.

Interestingly, if I open up the properties, make NO changes, then click "OK", the chart then comes up fine.

It wouldn't surprise me if there's something that's changed in how macros are supposed to add dimensions, so some property is a little off, and then just opening up properties and saying OK is somehow flipping it to an allowed value of something.

The API guide isn't any help. The examples seem to indicate that there's nothing special or new in regard to adding dimensions. And even gutting the macro and table (one expression, removing and readding a hardcoded dimension) causes the failure.

So I'm going to call it a bug, and it seems like a very basic bug with adding dimensions using a macro. If I come up with a workaround, I'll post it. Unless it's fixed in SR2, I'll need to come up with a workaround, because the current plan is to officially upgrade to version 10 once SR2 comes out.

Is anyone successfully adding dimensions using a macro in version 10 SR1?

Create table based on Fields selected in List Box

johnw — Thu, 24 Mar 2011 17:16:06 GMT


spencern wrote:Could you use a bookmark to save the selections of a table? Then the user wouldn't have make any other selections.

How would bookmarks allow us to dynamically add and remove dimensions and expressions from a table?

Create table based on Fields selected in List Box

johnw — Thu, 24 Mar 2011 17:44:00 GMT

OK, this is the simplest demonstration of the problem I could think of. I have only a single field, MyField. I have a straight table with no dimension and no expression that doesn't suppress zero values. I have this macro tied to a button.

sub test()
ActiveDocument.getSheetObject("CH01").addDimension "MyField"
end sub

In version 9 SR6, if you click on the button, the chart appears.

In version 10 SR1, if you click on the button, the chart doesn't appear. If you open the properties of the chart, you can see that the macro DID work, in that it added MyField as the dimension. If you then click OK, the chart pops up correctly.

This definitely looks like a bug to me. But I see nothing for "adddimension" searching the customer portal or the forum. Is there somewhere else I look for known bugs? Or does QlikTech just not publish that information? I don't want to report a bug if it's already been reported, and surely someone else has run into this before now.

Do others see the same behavior? And again, does anyone have a macro adding a dimension that works in version 10 SR1?

Create table based on Fields selected in List Box

johnw — Fri, 25 Mar 2011 00:47:26 GMT


Christian77 wrote: The thing is that neither mine nor yours work in QV 10.
What do we do?


John Witherspoon wrote:Another approach we've been looking into, but somehow never found the time to really do it, is to CLONE a hidden table and then remove all of the dimensions and expressions you don't need. Since each user would then have a different table ID, there wouldn't be conflicts to crash it.

I don't have the code cleaned and tested to the point where I'm happy posting it, but I thought I'd let you know that I have this approach up and running and will probably post it in the next few days if it all tests out OK. It appears to work fine in version 10.

I believe this fixes both errors I know about with the old approach - two users modifying the same object on the server could crash the application or hang their user session, and there have been mild to serious problems adding dimensions depending on the QlikView version.

Create table based on Fields selected in List Box

johnw — Fri, 25 Mar 2011 19:33:18 GMT

I decided to make a new thread for discussing the new approach and the new code.

http://community.qlik.com/forums/p/43233/171508.aspx#171508

Create table based on Fields selected in List Box

pover — Fri, 25 Mar 2011 21:00:11 GMT

John, I read this post after your other post, so I just want to add that I haven't reported it as a bug and I'm not sure whether it is reported. Since I found the bug when working on a personal project and not on a customer's application, I didn't take the time to send an e-mail to QV support since I also assumed that I wasn't the first. I hope everybody doesn't assume that...

Create table based on Fields selected in List Box

johnw — Fri, 25 Mar 2011 21:11:43 GMT

OK, I'll try to remember to report the bug next week using the example and the additional information you gave me in the other thread. Probably someone already has, but better to be sure.

Create table based on Fields selected in List Box

christian77 — Fri, 13 May 2011 11:24:57 GMT

Yes you can.

Some examples from APIGuide. Use a macro. Select your field. In the macro, GetPossibleValues, and then...

Rem Dynamic Data Update

sub Update
SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE * SET Discount = if(Discount >= 35, 0, if (City='Stockholm', Discount + 5, Discount + 2)) WHERE Country = 'SE'")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

sub Insert
SET Result = ActiveDocument.DynamicUpdateCommand ("INSERT INTO * (Country, City) VALUES (DK, Copenhagen), (NO, Oslo)")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

sub Delete
SET Result = ActiveDocument.DynamicUpdateCommand ("DELETE FROM CITY WHERE IsNull (Discount)")
if Result = false then
MsgBox Result.ErrorMessage
end if
end sub

Create table based on Fields selected in List Box

Wed, 18 May 2011 17:36:14 GMT

Hey John,

could you please explain to me how the expressions are defined in the macro. What tells the macro what kind of expression should be applied?

Best Regards

Thorsten

Create table based on Fields selected in List Box

johnw — Wed, 18 May 2011 23:45:41 GMT

The expressions are not defined in the macro. The expressions are defined in the table. The macro merely enables and disables them as required.