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

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

Thu, 31 May 2012 16:53:23 GMT

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

Thu, 31 May 2012 19:54:40 GMT

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 — Fri, 08 Jun 2012 08:20:10 GMT

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

Mon, 11 Jun 2012 14:02:33 GMT

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

-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

set v = ActiveDocument.Variables("varSWDeliverables")

v.SetContent A ,true

ActiveDocument.Reload

end sub

Would appreciate any ideas or suggestions.

Thanks.

Neena