Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gerhardl
Contributor II

When Bookmarked Selections doesn't exist

Hi there,

I ran into a potential nightmare, and I hope there is a way out:

I've been spending hours an a macro (already 5,000 lines long), which generates a report based on selecting bookmarks, then sending all the possible Account Numbers to excel, then selecting a random one and deleting the rest. Once I am done it will repeat this process for about 300 bookmarks (the macro might run for 30 minutes, but it will save us 3 to 4 hours work).

The problem is that certain bookmarks might have zero accounts. The data changes daily, so today bookmarkX might match 1 or 2 accounts, but tomorrow none.

When I select a bookmark that matches NO accounts, it will still activate the bookmark but it will make zero selections in the field where it cannot find a possible valaue.

What I want the macro to do is only select a bookmark if it exists and matches 100%, otherwise it must skip it and go on with the next bookmark.

I feel this is impossible - but PLEASE tell me it can be done.

Here is what my macro looks like (it pretty much just repeats itself for all bookmarks and changes the range in Excel; I've included 2 bookmarks here):

'<<<<<<Dormant Account - NO STATEMENT Product 5 (start)>>>>>>>>>

ActiveDocument.RecallDocBookmark "Dormant5"                                                                                        

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"                                                                

set LB = ActiveDocument.GetSheetObject("LB28")                                                                                   

boxvalues=LB.GetPossibleValues                                                                                                 

for i = lbound(boxvalues) to ubound(boxvalues)                                                                              

XLSheet1.cells(i+1,1).Value = boxvalues(i)                                                                                    

next                                                                                                                                 

XLSheet1.Range("B2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"                                       

XLApp.Worksheets(1).Range("B3") = XLApp.Worksheets(1).Range("B2")                                                      

XLApp.Worksheets(1).Range("B2") = XLApp.Worksheets(1).Range("B3")                                                      

XLApp.Worksheets(1).Range("B3").Delete                                                                                         

XLApp.Worksheets(1).Range("B1") = "5. ASA"                                                                                    

XLApp.Worksheets(1).Range("A:A").Delete                                                                                        

XLApp.Worksheets(1).Range("A:A").Insert                                                                                         

'<<<<<<Dormant Account - NO STATEMENT Product 5 (end)>>>>>>>>>

'<<<<<<Dormant Account - NO STATEMENT Product 6 (start)>>>>>>>>>

ActiveDocument.RecallDocBookmark "Dormant6"                                                                                  

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"                                                               

set LB = ActiveDocument.GetSheetObject("LB28")                                                                               

boxvalues=LB.GetPossibleValues                                                                                                  

for i = lbound(boxvalues) to ubound(boxvalues)                                                                               

XLSheet1.cells(i+1,1).Value = boxvalues(i)                                                                                   

next                                                                                                                                

XLSheet1.Range("C2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"                                      

XLApp.Worksheets(1).Range("C3") = XLApp.Worksheets(1).Range("C2")                                                    

XLApp.Worksheets(1).Range("C2") = XLApp.Worksheets(1).Range("C3")                                                    

XLApp.Worksheets(1).Range("C3").Delete                                                                                         

XLApp.Worksheets(1).Range("C1") = "6. HTSA"                                                                                  

XLApp.Worksheets(1).Range("A:A").Delete                                                                                        

XLApp.Worksheets(1).Range("A:A").Insert                                                                                        

'<<<<<<Dormant Account - NO STATEMENT Product 6 (end)>>>>>>>>>

Please let me know if you need more detail or if it doesn't make sense...

Thanks,

Gerhard

Tags (2)
1 Reply
gerhardl
Contributor II

Re: When Bookmarked Selections doesn't exist

I now have a macro which creates a chart showing me the number of Accounts that would match each possible bookmark.

it includes this:

ExpressionString = "Count(DISTINCT {'" & bookmarks(i) & "'} [Account no])"

Can I not add something to my Report macro (previous comment), that says if THIS expression is equal to ZERO, then the macro must skip the next X number of lines?


Community Browser