topic Re: Dynamic container with tableBox inside in QlikView

Dynamic container with tableBox inside

Tue, 03 Nov 2015 09:46:13 GMT

Good day everyone.

I have next issue.

As Data source I have qvd file with next structure

License Plate | Start Point | Destination |

AAAAA	Start Point1	Destination point 1
AAAAA	Start Point2	Destination point 2
AAAAA	Start Point3	Destination point 3

BBB	Start Point4	Destination point 4
BBB	Start Point2	Destination point 2
BBB	Start Point5	Destination point 6

For each license plate should be created separate table Box and all table boxes should be inside one container.

I was trying to create one TableBox with all information and after inside VB script for each License plate create separate table and put this table in ContainerBox.

Unfortunately I did not found any possibility to get just part of rows from General table and Paste it into new one.

From other side I also tried to run macro from load script.

Idea was to go through all license plates inside load script and for each part of datasource call VB macro which creates a table based on that part of datasource.

but still any success

Problem is that macro did not starts from load script.

Does anyone have any idea about this question?

Re: Dynamic container with tableBox inside

jerrysvensson — Tue, 03 Nov 2015 10:59:13 GMT

Regarding running macro from loadscript. It doesn't work in Publisher if you have a macro that needs system access.

Re: Dynamic container with tableBox inside

Tue, 03 Nov 2015 11:43:19 GMT

Step one would be, in my opinion, to create for each License Plate a seperate table in your datamodel.

LPList:

load Distinct

@1 as LicensePlate

Resident MainTable;

for i = 0 to NoOfRows('LPList')

Let vLplate = peek('LicensePlate', $(i), 'LPList');

Trace ========================$(vLplate);

QUALIFY*;

load * Resident Table where @1 = '$(vLplate)';

Maybe this will help you get further.

Re: Dynamic container with tableBox inside

rwunderlich — Tue, 03 Nov 2015 14:55:59 GMT

Here is a possible approach.

1. Create several Straight Table charts (not table boxes) in your container. Call them Lic1, Lic2, etc.

2. In the script assign to the unique License numbers to variables vLic1, vLic2, etc.

3. In the charts, use a calculated dim of

=aggr(only({<LicensePlate={$(vLic1)}>}LicensePlate),LicensePlate)

4. Use conditional show to hide the charts that don't have vLicN value.

You will have to pre-create the max possible charts.

-Rob

Re: Dynamic container with tableBox inside

Fri, 06 Nov 2015 15:30:05 GMT

Thank you Rod for the response.

Actually it might work.

What I've done is next:

1. - I created a listbox with $Table field

2. - For each item from this listbox I'm creating a separate pivot table inside VB Script

3. - And the last step should be like this - when table is being created it should be added to Container box, which was created in VB Script also. But here I met some difficulties.

Below you can see my VB Script code.

dim counter

sub GetAllItemsFromListBox

Set ContainerObj = ActiveDocument.Sheets("Main").CreateContainer

set ContProp=ContainerObj.GetProperties

ContProp.Frame.Name.v = "Selections"

ContProp.SingleObjectMode = 1

ContProp.SingleObjectStyle = 1

ContProp.ShowObjectTypeIcons = True

set lb = ActiveDocument.GetSheetObject( "LB01" )

h = lb.GetRowCount

counter =0

for RowIter=0 to h-1

rem 2. Create charts for all tables in tableModel

set chart = ActiveDocument.Activesheet.CreatePivotTable

chart.AddDimension lb.GetCell(RowIter,0).Text&".@2"

chart.AddDimension lb.GetCell(RowIter,0).Text&".@3"

chart.AddExpression "["&lb.GetCell(RowIter,0).Text&".@4]"

chart.AddExpression "["&lb.GetCell(RowIter,0).Text&".@5]"

chart.AddExpression "["&lb.GetCell(RowIter,0).Text&".@6]"

chart.AddExpression "["&lb.GetCell(RowIter,0).Text&".@7]"A

set cp = chart.GetProperties

cp.TableProperties.IndentMode = true

cp.TableProperties.NumberOfLeftDimensions = 2

cp.GraphLayout.Frame.ObjectId = lb.GetCell(RowIter,0).Text

chart.SetProperties cp

ContProp.ContainedObjects.Add

ContProp.ContainedObjects.Item(RowIter).Id = "Document\"&lb.GetCell(RowIter,0).Text&"]"

ContProp.ContainedObjects.Item(RowIter).Text.v = "text"

ContainerObj.SetProperties ContProp

end Sub

Anyway we found other way for presenting data )

Thank all of you for the help