Looking for a way for comment (or "tag") specific fields.

[#1]

Hi everyone!

I am trying to create functionality for commenting (or "tagging") specific field values within the QlikView interface. For now I have built it around some ad-hoc ideas, but now I wonder if there is a built in function or an easier way that could replace my rather improvised solution.

This is the problem: I have a field called "Part No" that contains around 75000 distinct values. What the client would like to do is to select some of these Part No:s and enter a comment, or tag, for them. For future analysis. Like the table below. So, basically the values in the Comment field are put in by the user within the QlikView interface. (I would really like to avoid having to export to Excel or other editing software as an intermediate step).

Part No	Part Comments
1001	Status: OK
2232	Status: OK
3431
4893
4678	in progress
5008	Status: OK
6180
8432
9782	in progress
9904	Status: OK

This is my ad-hoc solution:

1) An input field where the users enter their comment. Stored in the variable vComment.

2) A chart with Part No as the dimension and $(vComment) as the expression. (this chart is referred to as "PC")

3) A button that executes a macro that takes the chart and appends it to a .csv file stored in the working folder. The application then reloads itself.

    set obj = ActiveDocument.GetSheetObject("PC")    

    obj.ExportEx "Part_comments.csv", 1, true, ";",0

    ActiveDocument.ReloadEx 0,0

Now, the user will have a field called "Part Comments" available and can continue their work session with the newly added comments.

How can I improve this? I'm sure there must be much better and easier ways.

Best regards

Alexander Mosquera

Gothenburg, Sweden

[#2]

Actually I found that the GetPossibleValues can take on more than 100 if you add the parameter like this:

GetPossibleValues(2000)   <-- This raises the limit to 2000 items

I'm gonna try how many you can take on without severe performance loss.

I'll get back to you soon with the results.

[christian77]

Well, that´s really good. I didn´t know it. That fact is gonna save lots of loops.

Thanks a lot.

[#4]

Ok, so here are some test results. It seems like the for-loops are quite heavy and that the time required is proportional to to the number of the items in a linear way, roughly.

The whole Part No field that I'm working with have 76000 distinct values which I imagine would take forever to run - at least 7600 seconds (>2 hours)

The script that I used initally is stilll faster:

100 items - 18 seconds

76000 items (the whole field) - 1 min 30 seconds

What's your eperience here? How many items do you run simoultaneously and how long does that take?

Btw, you have been extremely helpful here. Thanks for the friend request as well.

[christian77]

QV is not initially meant to do that. Using macros saves you sometimes but, do they work on the server? And, how they perform?

I used that loop for a client who wanted to create a different pdf per value of a field (Business Unit) and send it attached to every Business Unit, each one its own report in pdf.

There were about 100 BU. It works fine in the server also.

Also, I used once to perform a real incremental reload, never dropping any table. So there was no need to reload qvd.

Even when the time that you took, 18 seconds for 100 records, is real, it does not mean that 1000 records will be 180 seconds. It doesn’t work like that. It’s only an approaching.

If we are talking about 76000 records, I´d think in another way. Are those 76000 records to be updated every day, or it is only the initial situation?

I don´t know what to tell you at this point. They are too many. The first thing coming to my mind is doing the task by parts. Group somehow your records and do 1000 at a time. Divide and you´ll win. I know you don´t want to use intermediate applications like excel.

Using the expression '<url>' & only(your excel doc address) in some table, you can bring (open) the doc from QV, and modify it there. Or adding an action to a textbox.

Ok.

[christian77]

By the way is GetPossibleValues(76000) working? That's a huge variable.

[#7]

You're, right. Excel is still the king when it comes to entering comments or other highly unspecified data input. Thanks for sharing your experience though.

Regarding my 76000 records,let me just breifly describe what I'm aiming for with all this. The part numbers are updated quarterly and there are already existising natural groups or categories within them. These groups are ususally determined by dimensions that comes with the source data. For example, there are categories for what factory they are produced in, which  design department they belong to, and several facts such as the consumption per factory, forecast volume, etc etc.

Most of these categories works perfectly fine but what they cannot provide is follow-up status. The client uses my application to find business cases within their data, and when they do find interesting things they need to place a comment or tag that says fo example "These part numbers need attention" or "talk to Susan about these ones" or "why are these parts used less in Q4 than Q3?", and so on. When they have examined a business case they need to go back to that certain group, pick up where they started and eventually change the tag to "Done" or "Unsolved" or something else. Today they are doing the follow-up in Excel and switches back and forth between QV and Excel. So they do not have a good workflow process since they do no not see what they have processed and what they have not in QV.

After I tried GetPossibleValues(1000) I realized that GetPossibleValues(76000) would be too heavy. As you said, the variable is huge.

[christian77]

What about an application is .NET?

QV can make interaction in a database like SQL, if you configure it to do so, but as I say, this tool is to get data and analyze it.

[softsystemit]

Hello!

I would like to contribute something I discovered, having problems before.
Before update has to know if you can.

'If the dynamic update is not active does not work, then we set and it is already possible to use update!

    set docprop = ActiveDocument.GetProperties

    docprop.EnableDynamicDataUpdate=true

    ActiveDocument.SetProperties docprop

[christian77]