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

[christian77]

Hi:

Well, what I do in this case is use Dynamic Update Commands.

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

WATCH: 1st go to Document Properties, Server, and check Allow Dynamic Updating Data

And then export to QVD.

set tb = ActiveDocument.GetSheetObject("CH01")

tb.ServerSideExportEx "C:\test.qvd" , ";" , 4                  '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF

You'll save the Reload and keep your data on the qvd.

Before exporting, clear your selections. Table will export as it’s shown, with label names included.

Is similar to yours.

If you need it, I´ll send you an example.

[#3]

Hi Christian

Thank you for the quick reply!

I tried your solution but the Insert command did not work. Please, if you don't mind, attach an example.

And regarding exporting to qvd instead of csv, I tried it before but I do not think you can append new rows to an already existing qvd. You van only overwrite it with new data. Appending works with csv.

Please inform me on this matter.

Best Regards

Alexander M

[christian77]

Alexander, have you enable the feature.

[christian77 · Accepted solution]

Hi again:

I don´t know how to post a file. They are maintaining the page and it’s not finished yet. Yes, if you need to add the record, use csv. I always prefer qvd. It’s fast, even when there are many records.

SET Result = ActiveDocument.DynamicUpdateCommand ("INSERT INTO TABLE NAME (Field1, Field2) VALUES (" & mVar1.GetContent.String & ", " & mVar2.GetContent.String & ")")

In the macro, you need to pick up the variables first.

Set  mVar1 = ActiveDocument.Variables(“Var1”)

If you use macros, you need to find APIGuide in your QV documentation. It’s so useful.

The thing is that is 18:40. This is Spain. I need to go now. Tomorrow more.

[#6]

Hi again,

I got it to work after your last post yesterday. The INSERT command works perfect and really fast!

I think that my problem before was that I used ' instead of ", or the other way around...

The problem now is how to make it work for multiple items. In other words the user needs to be able to add a comment for multiple Part Nos in one go. So I am thinking that 2 things need to happen (but I am not sure about how):

1. First the possible values in the Part No field need to be parsed through to the macro.

2. The INSERT needs to run once for each item in the parsed Part No list.

For problem 1 I tried the QlikView function

=GetFieldSelections("Part No")

as the mVar2 variable definition, which produces a comma-separated lists of the selected Part Nos. However, it only works actual selected values and not for possible values, and it only work for 6 or less selected values. When I select more than 6 values the variable value changes from a list to something like "10 of 72340". So the

=GetFieldSelections("Part No") does not handle large selections well.

On the 2nd problem I think that maybe a for-loop aroudn the INSERT might work.

[#7]

Oh, and thanks again for taking time btw.

[christian77]

Hi Alexander:

You need to have APIGuide. It is located in C:\Program Files\QlikView\Documentation.

Go to automation examples and search what you want. Since the value is in a variable is going to be hard to make it work.

Ok.

[#9]

I have gone through the API and this is my current solution. I use the function GetPossibleValues to produce the list of items to be commented on. But, it only takes a maximum of 100 items and no more. It seems there are no function that is able to take out all of the possible values.

sub Insert
      set comment=ActiveDocument.Variables("vPartCommentValue")                    
      set listOfPossibles=ActiveDocument.Fields("Part No").GetPossibleValues 
      for i=0 to listOfPossibles.Count-1
        ActiveDocument.DynamicUpdateCommand ("INSERT INTO 'Part_comment_1' ('Part No', 'Part comment 1') VALUES (" & listOfPossibles.Item(i).Text & ", " & comment.GetContent.String & ") ")
      next
end sub

[christian77]

Exactly. You’ve been fast. GetPossibleValues only takes 100 values and no more.

QV stores the information in tables, not in variables.

You may create an array, but I´m not sure of the functionality.

Try this other one. 1^st, deselects everything. 2^nd selects one by one and gets the value.

ActiveDocument.Fields("Field1").Select ""
set Valores=ActiveDocument.Fields("Field1") 

for i=0 to Valores.GetCardinal-1                                    

     ActiveDocument.Fields("Field1").Select (i+1)   
     set val7=ActiveDocument.Fields("Field1").GetPossibleValues    

next