3 Replies Latest reply: Jun 11, 2012 10:02 AM by nrodricks RSS

    how to display table content based on content(variable) from ListBox

      I have a list box with names to the columns of a Table (Table is System with Column Names(Version, platform,Source....)


      My script:

      //connect to the Database



      LOAD * inline


      SWDeliverables //name




      //So now the listbox gets the values from TempSWDeliverables

      LOAD * RESIDENT Deliverables;



      //Now I set the variable SWDeliverables=getFieldSelection(TempSWDeliverable) using the Document Properties

      SQL SELECT distinct '$(SWDeliverables)' AS SelectedSW, s.id as ID
      FROM TableSystem s where s.id= 10;
      Load *RESIDENTtableSWDeliverables;

      My problem is  I am unable to display the table contents based on the selection .

      Would appreciate any suggestions/ideas.



        • how to display table content based on content(variable) from ListBox

          Could somebody please suggest any solutions as I have to dynamically be able to run the Query and display the table contents AFTER i Have selected from the Listbox.


          Since its dynamic and I have linked the variable to the Listbox --I am not able to display the table.

          But if the variable is predefined as 'Version' then the SQL works.



          Thanks in advance.

            • how to display table content based on content(variable) from ListBox
              jagan mohan rao appala



              You should not filter the data in Script initially, you should all the data.  Qlikview automatically filters data if you select any value in Listbox if there is mapping / link between the fields.  This is the default behaviour of Qlikview. 


              You should not load the file everytime you select in Listbox.


              Can you attach sample file and with detailed description of what you want.




                • Re: how to display table content based on content(variable) from ListBox

                  Thanks so much for replying.

                  I finally was able to load the data the QlikView way.


                  I have 3 tables for now and they are already connected thru keys in the Database.

                  Master has ID

                  System- is related to Master thru ID

                  BC- is also related to Master thru ID – The BC table has 1 record for each system while Master and System (both have same total number) can more than 1 record for the same system


                  So I used:

                  Load * from master//not following the syntax(just for example)

                  Sql Select * from master;

                  Load * from system//not following the syntax(just for example)

                  Sql Select * from system;

                  Left join Load * from BC

                  Sql Select * from BC;


                  Please let me know if I am right till here.


                  Now since I have the data loaded:


                  I have a multi-box that has all but 2(which is id, sys_id) the fields in it.


                  Problem: I need to display a chart(ONLY ONE) which will chart out Field against the count(distinct (Master.serialNums))

                  Based on my selection of the mulitbox.


                  So if my mulitbox has (Actually all I need is the names of the Field in the mulitbox- I don’t know how to get that only and no need for Values)







                  -Operating System

                    Windows Enterprise

                  Windows Standard


                  If I select Version I should be able to plot in the chart:

                  Version against count(distinct (Master.serialNums))


                  In the same chart if I select something else :

                  ROM against count(distinct (Master.serialNums))


                  I don’t know how to use the Variable here or trigger in this case.  Please find the attached jpg file.


                  Previously I used a variable that varSWDeliverables  and then had a sql statement for each selection that would query everytime the Dbase by using a Reload script using a trigger.


                  IF wildmatch('$(varSWDeliverables)', 'SYS_iLOVersion') THEN  // Only sheetNames that begin "Sales"



                  SQL select distinct A.product as Product,A.SYS_iLOVersion as SWDeliverable,A.Supplier as Supplier,A.Source_ as Source,

                          COUNT(A.Count) as Count,A.Year as Year,A.Week as Week



                          select s.sys_product as product,s.SYS_iLOVersion as SYS_iLOVersion,

                          s.SYS_Source as Source_,b.BC_WWSiteName as Supplier,

                          COUNT(distinct(m.AHS_SYSSerialNumber)) as Count,year(cast(b.BC_BuildTimestamp as Datetime)) as Year,

                          datepart(week,cast(b.BC_BuildTimestamp as Datetime))as Week

                          from “System" s, “MASTER" m

                          left join "ahs_bc" b

                          ON  m.ahs_id=b.ahs_id

                          where m.AHS_ID=s.AHS_ID

                          group by s.sys_product ,s.SYS_iLOVersion,s.SYS_Source,b.BC_WWSiteName,cast(b.BC_BuildTimestamp as Datetime)

                          ) A

                          group by A.product, A.SYS_iLOVersion,A.source_,A.Supplier, A.Year,A.Week

                          order by A.Week;


                  Script to select the variable from the list box and reload the document:

                  Sub Test

                     set doc = ActiveDocument

                     set mySelections = doc.fields("SWDeliverables").GetSelectedValues


                     for i = 0 to mySelections.Count - 1

                          if len(trim(A)) = 0 then

                             A= mySelections.Item(i).text


                             A= A  & "," & mySelections.Item(i).text


                          end if



                     set v = ActiveDocument.Variables("varSWDeliverables")

                     v.SetContent A ,true



                  end sub




                  Would appreciate any ideas or suggestions.