Help with complicated selections and exclusions

[#1]

Hi All,

I have been using Qlikview for data exploration and spend analysis. It's a really great tool but I need some ideas around a particular problem.

I select data using a variety of different fields and text searches to identify a baseline of spend for a particular category. My main issue is that I often have to use multiple searches and selections and I cannot apply them all simultaneously to get a single complete baseline in Qlikview. Furthermore if the searches produce overlapping result sets I want to remove anything previously selected so I don't double count.

Of course I can export it to excel and build up the full set of data but this is not ideal and doesn't fully resolve the double counting. I've come up with a few ideas to get around this problem but I would appreciate expert opinion.

Option 1) Export each search result set into Excel. Connect to these export files in the load script of the QV and match against the original rows. Where the row matches, mark the records with a flag, such as "selected". Use this flag in the QV front end to apply selections and exclude selections to ensure duplication doesn't occur.

Advantages; Resolves the above issues around double counting and aggregating multiple complex selections

Disadvantages; Fairly clunky process. Not sure how much could be automated

Option 2) If possible write a script to attach the flag indicated above within QV. I don't know if this is possible as I don't know if I can use selected data as criteria for an update, and if the data model is updatable anywhere else other than the import script.

Advantages; Keeps it all internal to QV and should be fully automated

Disadvantages; Don't know if it is possible

Option 3) Output the results of all selections to excel and run macros to identity double counted spend.

Advantages; Resolves the issues mentioned above

Disadvantages; Lose flexibly of QV to further interrogate final data selections

I think this is a really interesting problem and would appreciate any feedback and experience of others.

Alex B

[lironbaram · Accepted solution]

hi alex

attach is an exmple of some sort of solution:

the first part is using Bookmarks

which allows you to save multiple choices as one choice

the seconed part is using set analysis in the expressions it allow you to exlude double records

check the attach file

i mdae 3 bookmaraks if you select each one of them you can see what are the multiple selections are

hope its helps you

i'll be glad to help you if yu have mpre questions

[#3]

Thank you for your reply. I'm using personal version so I can't open your file however I think I understand your ideas but I don't think they address my issues.

Bookmarks; Is it possible to UNION DISTINCT results of two bookmarks together, as this is essentially what I need to do.

Set Analysis; The problem isn't in the data or the expressions used in the UI, its with how the selections are formed when data is being selected.

I'll try and illustrate the problem as best I can with some examples;

Case: Identification of spend for "Networking infrastructure" spend

Search 1) Keyword search in transaction description field for "Networking" -> Yields results that match requirements

Search 2) Selection based upon specific field value (not search but data selection, in this case Vendor Classification) - Yields results that match requirements but also includes some of the transactions found in the "Networking" keyword search.

Search 3) Selection based upon specific field value (in this case GL code) - Yields results that match requirements but also includes data from Search 2 and Search 1.

Each search yields additional data that is required but may also include data found in the previous search.

What I need to do is find a way to select all of these search results at once so I can use the other reports I have to navigate this area of spend. I already use the set analysis to prevent duplication within the search but I cannot actually perform all of these searches at the same time as they overlap criteria and have multiple searches on the same field.

If the searches are simple its not so bad as I can use the "select exluded" facility to deselect records already identified but there must be a more robust way around it.

I was thinking that if I can identify in the base data the rows returned by the various searches, I could flag them and then include / exclude them as I need to so I'm trying to find the best way of achieving this.

Thanks for your help!

[lironbaram]

hi

but that exctly what i've done with the bookmark

think of that like this

make a book mark for each case you described

let say each bookmark gives you a list of pepole

bulid a chart

with the pepole id as dimension

and in the expressions put sum({BM01}Spend),then sum({BM02-BM01}Spend),and so on

this way the second expression take all the pepole from the seconed case which are not in the first case ext

hope that clerify what i did

give it a try if not il give you a doc with the script and the expressions i used

[#5]

Ahhh ... I see. I didn't know you could reference bookmarks in expressions. Is it possible to create bookmarks programmatically and build the expressions programatically. I assume it is but I don't know how open the API is.

I'll have to think of the best implementation for this because as it stands it wouldn't work too well in the UI. It would be handy if I could create a button when each time it is clicked it creates a bookmark for the current selections, and then updates all of the expressions on a particular tab to include all of the bookmarks. I could then create an accumulated data output tab that used the expressions data.

You have pointed my in the right direction, thanks! Any further advice regarding the automation / scripting would be welcome.

Alex B.

[#6]

This is what I have quickly hacked together;

sub outputBookmarks
 bookmarks = ActiveDocument.GetDocBookmarkNames
 for i = 1 to UBound(bookmarks)
 strSetExp = strSetExp & "[" & bookmarks(i) & "]+"
 next

 strSetExp = Left(strSetExp,Len(strSetExp)-1)
 strSetExp = "{" & strSetExp & "}"

 set objST = ActiveDocument.GetSheetObject("CH89")
 objST.AddExpression "Sum(" & strSetExp & "NET_Amount)"
end sub

It builds the expression ok but when I look at the table it has a big cross through it. I click on the table properties and just click on ok to close it again, and it loads the data fine just as I want it.

Do I need to do a programmatic refresh or repaint? Am I missing something?