topic Set Analysis in visual Basic Macro (select a field) in QlikView

Set Analysis in visual Basic Macro (select a field)

Mon, 22 Aug 2011 14:51:38 GMT

Hi all,

(thanks in advande for any suggests)

I'm trying to select a field using a visual basic macro with set analysis or only max function, but it doesn't work:

the code:

[...]

Set LBy = ActiveDocument.GetSheetObject("LB09")

Set LBydate= ActiveDocument.GetSheetObject("LB08") //in this object there are a list of MY_DATE

valy = LBy.GetPossibleValues

valyDate = LBydate.GetPossibleValues

for y=lbound(valy) to ubound(valy)

vSelectedItemy = valy(y)

Set Fieldy = LBy.GetField

Set FieldyDate = LBydate.GetField

Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

Fieldy.Select vSelectedItemy

ActiveDocument.GetApplication.WaitForIdle

FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

'fine aggiunta igor

Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

ActiveDocument.GetApplication.WaitForIdle

In row :

FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

This expression doesn't return any error, but no MY_DATE is selected... also this doesn't work(more simple):

FieldyDate.Select("max(MY_DATE)")

but max(MY_DATE) and max({<LOCATION = 'SameLocation'>} date(MY_DATE)) work if I put it in a button as action :S ... How is it possible?

Thanks for any suggest!

Set Analysis in visual Basic Macro (select a field)

Mon, 22 Aug 2011 20:43:53 GMT

I think you're trying to make this more complicated than it probably is.

The statement below is your first problem:

FieldyDate.Select("max({<LOCATION = 'vSelectedItemy'>}date(MY_DATE))")

You're trying to make two selections and once for some reason. The set analysis is just another selection, so why not just select it separately?

Look at the example below:

'set up a variable called vMaxDate

'=max(MY_DATE)

set x= ActiveDocument.GetField("MY_DATE")

set y = ActiveDocument.GetField("LOCATION")

y.Select vSelectedItemy

maxDate = ActiveDocument.Variables("vMaxDate").GetContent.String

x.Select maxDate

'first it selects vSelectredItemy (which is what you have it doing in the set analysis). Once it does that,

'the variable changes to whatever the max date is, so you can just select maxDate

Also I didn't know you could GetPossibleValues from a list box but that doesn't mean you can't.

Hopefully some of that made sense

Re: Set Analysis in visual Basic Macro (select a field)

Tue, 23 Aug 2011 07:13:34 GMT

Trent Jones, thank you very much, your reply is very useful for me! : )

(Even if: in the case of need i cannot use set analysis into macro )

FYI (and for everyone in the future had my problem) this is the macro that i use for iterate into a field (location), select a max for an other one (max POST_DATE for each location) and finally export a single table with different selection in an unique excel file, it works :

sub PrintDetails

' Set Excel App

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

'XLDoc.Worksheets("Sheet1").Delete

'XLDoc.Worksheets("Sheet2").Delete

' Get TB1

Set LBy = ActiveDocument.GetSheetObject("LB09") 'must be a List Box objext, it contains my list of Location

Set LByDate = ActiveDocument.GetSheetObject("LB08") 'must be a List Box objext, it contains my list of post_date

Set FieldyDate = LByDate.GetField

valy = LBy.GetPossibleValues

for y=lbound(valy) to ubound(valy)

vSelectedItemy = valy(y)

Set Fieldy = LBy.GetField

Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

Fieldy.Select vSelectedItemy

ActiveDocument.GetApplication.WaitForIdle

FieldyDate.Select ActiveDocument.Evaluate("max(POST_DATE)")

Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds

ActiveDocument.GetApplication.WaitForIdle

'Riga commentata mi serviva per debug dell'espressione

'MsgBox(ActiveDocument.Evaluate("max(POST_DATE)"))

set table = ActiveDocument.GetSheetObject("CH05")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets.Add 'Select sheet where data should be pasted

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell

XLSheet.Name= valy(y)

next 'Year

Fieldy.Clear

'XLDoc.Worksheets("Sheet3").Delete

end sub