Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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
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.
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.
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