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

       


      Deliverables:

      LOAD * inline

       

      [
      SWDeliverables //name
      Version

      Platform

      OperatingSystem

      ];


      //So now the listbox gets the values from TempSWDeliverables


      TempSWDeliverables:
      LOAD * RESIDENT Deliverables;

       

       

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

      tableSWDeliverables:
      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.

      Thanks

       

        • 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

              Hi,

               

              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.

               

              Regards,

              Jagan.

                • 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)

                  -Version

                    5

                  6

                  -ROM

                  2/5/2011

                  3/4/2012

                  -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.

                  Script:

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

                  SYS_iLOVersion:

                   

                  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

                  from

                          (

                          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

                          else

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

                   

                          end if

                   

                     next

                     set v = ActiveDocument.Variables("varSWDeliverables")

                     v.SetContent A ,true

                   

                     ActiveDocument.Reload

                  end sub

                   

                   

                   

                  Would appreciate any ideas or suggestions.

                  Thanks.

                  Neena