topic Help!! How to send array values to tablebox? in QlikView

Help!! How to send array values to tablebox?

Sat, 26 Jan 2013 04:58:20 GMT

Hi All
i have an array in vbs macro

Sub CreateArray
  Dim MyArray() ' dynamic array
  LengthOfArray = 3 ' Length may differ from time to time
  Redim MyArray(LengthOfArray)
  For i = 0 to LengthOfArray - 1
    MyArray(i) = 2*i + 1
  Next
  
  ' check array values in message boxes
  For i = 0 to LengthOfArray - 1
    MsgBox MyArray(i)
  Next
End Sub

How can i create a tablebox and send values of array in the created tablebox?

Re: Help!! How to send array values to tablebox?

Gysbert_Wassenaar — Sat, 26 Jan 2013 10:07:59 GMT

Short answer: you can't

Long answer: You can, but....

There is no way to directly assign an array to a table box. Qlikview is not a spreadsheet. A table box can only display data from the internal database. The internal database is created by the load script during (re)load. You first have to create a table in the script for your data. If you have QV 11 you can use Dynamic Update to add/update/delete data in the internal database. So if you create a table in the script you can use dynamic update to add your array data to the table. Then you have something that can be shown in the tablebox. See this document for the com api details for creating a table box and using the activedocument.dynamicupdatecommand method.

Re: Help!! How to send array values to tablebox?

Sat, 26 Jan 2013 12:16:25 GMT

Thank you, Gysbert
i've made it using *.csv

maybe my experience will be helpful to somebody

macro is:
Sub WriteToCSV
Dim objFSO, file, str
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set file = objFSO.CreateTextFile("MyFile_01.csv")
Dim BufArray()
LenBufAr = 10
Redim BufArray(LenBufAr)
BufArray(0) = "NULL"
file.writeline BufArray(0)
BufArray(1) = "NULL"
file.writeline BufArray(1)
BufArray(2) = "NULL"
file.writeline BufArray(2)

for i = 3 to LenBufAr-1
'BufArray(i) = replace((i*i)/10,",",".")
BufArray(i) = replace(10*sqr(i),",",".") ' replace comma with point
file.writeline BufArray(i)
Next

'for i = 0 to LenBufAr-1
'MsgBox BufArray(i)
'Next
MsgBox "Ok"
End Sub
'------------------------------
sub Reload
set varCurrentQvw = ActiveDocument
varCurrentQvw.PartialReload
end sub

reloading script is:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Data0:
LOAD
RowNo() AS Hdr_01,
@1 as @1_01
FROM
[MyFile.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);

Data:
Replace LOAD
RowNo() AS Hdr,
@1
FROM
[MyFile_01.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);

so to reload only data generated by the script used "Replace" in the script
to avoid incorrectness when reading from MyFile_01.csv we replaced comma with point in macro