topic Re: Loop through all possible selections & display, store or export value from a txt object in QlikView

Loop through all possible selections & display, store or export value from a txt object

rutger_jansen — Sun, 16 Sep 2018 00:27:15 GMT

Hi,

I have a document with some list boxes and a text box. I want to loop through all possible selections and make the value of the text box for each selection available to the user.

I have initially tried cobbling a VBA macro together that exports the value for each selection into a single Excel sheet but I'm not sure this is the best approach, so hoping someone can suggest a more idiomatic way to do this.

Background: a large set of metrics is collated into a single score in order to rank subsets of the data according to their level of interest. Rather than a user manually traversing hundreds of possible combinations, I want to automate that and present them with the subsets/selections that are most relevant.

I have added a sample file that (almost) does what I want it to. It's good enough to give an idea of what I'm trying to achieve.

Thanks,

Rutger

Re: Loop through all possible selections & display, store or export value from a txt object

Frank_Hartmann — Sun, 16 Sep 2018 11:19:57 GMT

You want to get rid of duplictated rows? try this:

Sub ExportCharts() 
ActiveDocument.ClearAll()   
set XLApp = CreateObject("Excel.Application")   
XLApp.Visible = True   
set XLDoc = XLApp.Workbooks.Add   
set XLSheet = XLDoc.ActiveSheet   

set field1Values = ActiveDocument.Fields("Asset_Class").GetPossibleValues 
set field2Values = ActiveDocument.Fields("Business_Unit").GetPossibleValues 
set field3Values = ActiveDocument.Fields("Segment").GetPossibleValues 

chartList = ActiveDocument.ActiveSheet.GetGraphs 

for i = 0 to field1Values.Count - 1 
for j = 0 to field2Values.Count - 1 
for k = 0 to field3Values.Count - 1 
ActiveDocument.ClearAll() 
ActiveDocument.Fields("Asset_Class").Select field1Values.Item(i).Text 
ActiveDocument.Fields("Business_Unit").Select field1Values.Item(j).Text 
ActiveDocument.Fields("Segment").Select field1Values.Item(k).Text 

LastRow = XLSheet.Cells(XLSheet.Rows.Count, "B").End(-4162).Row   
next 
next 
With XLSheet   
.Range("B" & LastRow+2).Select  
   .Range("B" & LastRow+i).Select  
   .Range("B" & LastRow+j).Value = ValueToSelect 
   .Range("B" & LastRow+j & ":D" & LastRow+2).MergeCells=True
   .Range("B" & LastRow+k).VALUE = ActiveDocument.GetSheetObject("TX01").GetText() 
End with 

next  

end Sub

Re: Loop through all possible selections & display, store or export value from a txt object

rutger_jansen — Sun, 16 Sep 2018 17:02:12 GMT

Thanks Frank,

That does de-dupe my solution but it's not exactly what I'm looking for.

I'm trying to to export the value in the text box for every combination of selections across list boxes but with only one selection at most from each list box (the last part wasn't clear from my first post). So in my example selections can have one, two or three values.

Ideally, I want the Excel sheet to look like this:

Cars - Score (and all other scores for one value from one list box selected)
Cars & BAU - Score (and all other scores forone value each from two list boxes selected)
Cars+ Solution + UK - Score (and all other scores for one value each from three list boxes selected)

In my real document, each combination will have a different value in the text box.

I thought that the code above would give me all scores for one value each from three list boxes selected but it only shows single selections from a single list box...any ideas?

Re: Loop through all possible selections & display, store or export value from a txt object

Frank_Hartmann — Sun, 16 Sep 2018 18:02:39 GMT

if i find the time tomorrow i will have a deeper look into it!