Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Get first 100records of any logical table with vbs

Hi to all,

how can i get the first 100 rows of any logical tables using the vb macros (not script)?

Thanks

1 Reply
Not applicable

Re: Get first 100records of any logical table with vbs

Hi,

you can create a new sheet into your Qlikview document (you can hide it) and insert a table box object (example with ID = "TB01"). Next you add all the fields of your data table to this table box object. If you don't want current selections affect data lines into the table box create a new "alterante state" into your document and connect it to the table box. To the sure to extract the first 100 rows exactly as you have load it, you can crate an automatic  row counter  (using    RowNo()  AS IDRow    into your realod script)  and order data into table box using this field.

Copy and paste the following code into your VBS module :

' ***********************


Private Function PadLeft(value, pad_text, length)

  ' Pad on left.

    PadLeft = Right(String(length, pad_text) & value, length)

End Function

' ***********************

' Extract data from a QlikView table (formatted text lines)

'  IDSheetObject : QlikView object ID (example : TB01)

'  startRowIndex :  0,1,2,... Index of row where to start read lines (0=colums title, 1=first data row., 2= second data row, ...)

'  endRowIndex   :  0=read all rows in tale,  N (>= 1) read only N rows (example 100 to read only 100 rows)

Private Function GetTableTextData(IDSheetObject, startRowIndex)

  ' Var

  Dim table

  Dim colCount

  Dim rowCount

  Dim cellMatrix

  Dim rowIndex

  Dim colIndex

  Dim sRet

  Dim index

  Dim t

  Dim vbCrLf

  sRet = ""

  ' Initialize objects

  Set table = ActiveDocument.GetSheetObject(IDSheetObject)

  colCount = table.GetColumnCount

  rowCount = table.GetRowCount

  If rowCount <= 1 Then

  ' Error : no data to process. (rowCount=1 : only the first row is present, this row is columns titles)

  Call MsgBox("Can't extract data from QlikView table object.", 48, "Warning")

  GetTableTextData = ""

  Exit Function

  End If

  Set cellMatrix = table.GetCells2(0, 0, colCount, rowCount)

  vbCrLf = chr(10) & chr(13)

  sRet = ""

  ' Read any row (if startRowIndex=0, read also the columns title)

  index = 0

  For rowIndex = startRowIndex to rowCount - 1

  index = index + 1

     For colIndex = 0 to colCount - 1

      ' Create text line (values format is defined into table object)

     fieldValue = cellMatrix(rowIndex)(colIndex).Text

     sRet = sRet & PadLeft(fieldValue, " ", 10)

     Next

     If (endRowIndex > 0 AND index = endRowIndex) Then

      ' Read only the first endRowIndex rows...

      Exit For

     End If

     sRet = sRet & vbCrLf

  Next

  ' Data read termintaed, return data as string lines

  GetTableTextData = sRet

End Function

' ***********************

To use the function you can call it as the following example :

' Read first 100 lines of data plus the columns title

txtLines = GetTableTextData("TB01", 0, 101)

' Read only first 100 lines of data (without columns title)

txtLines = GetTableTextData("TB01", 1, 100)


Community Browser