topic looping through all rows in a transactiontable with a macro in QlikView

looping through all rows in a transactiontable with a macro

Tue, 15 Dec 2009 22:50:54 GMT

I have a transaction table containing theese records:

(Sorry for bad formating)

period lime_itemno lime_qty
1 66-HRG22-1 267
2 42-JRD22-1 267
2 42-DRGGR-1 800
3 42-RRE2-1 267
3 42-DRG6-1 100
3 42-DRG6-1 100

How do i write a macro that steps through all theese rows, one by one? As you can see, some rows are duplicated, but I still concider theese rows to be unique and I want to step through theese as well.

I've tried this:

sub test
set period = ActiveDocument.Fields("trans_period").GetPossibleValues(100000)
for i = 0 to period.count -1
set itemno = ActiveDocument.Fields("lime_itemno").GetPossibleValues(100000)
for j = 0 to itemno.count -1
set qty = ActiveDocument.Fields("lime_qty").GetPossibleValues(100000)
for k = 0 to qty.count -1
msgbox period(i).text + "-" + itemno(j).text + "-" + qty(k).text
next
next
next
end sub

But that don't work, since I get too many messageboxes, one for each unique combination of all three columns.

Thanks,

/ Robert

looping through all rows in a transactiontable with a macro

Wed, 16 Dec 2009 23:40:00 GMT

Consider adding a unique rownumber to the transaction table, then create a tablebox with all the 4 fields. Rownumber as the 4th column.
This will now show all 6 rows.
Then use the following macro to loop through the rows (excluding the 4th column with the rownumber).
I would also use a different separator since the itemNo field has - characters in it.

sub test
set TableBox = ActiveDocument.GetSheetObject( "TB01" )
for RowIter = 1 to TableBox.GetRowCount-1
concatText = ""
for ColIter =0 to TableBox.GetColumnCount-2 'Don't include the last column (row)
set cell = TableBox.GetCell(RowIter,ColIter)
concatText = concatText + cell.Text + ";"
next
concatText = left(concatText, len(concatText)-1) ' Remove the trailing ;
msgbox(concatText)
next
end sub

Hope it's what you we're looking for.

/Fredrik

SV:Re: looping through all rows in a transactiontable with a macro

Thu, 17 Dec 2009 01:09:11 GMT

Elegant!

This script is perfect!

Thank You very much!

/Robert