topic Re: Variables with Direct Discovery in QlikView

Variables with Direct Discovery

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I want to perform following operation:

1. Load distinct customerid from table (in ListBox) (table T1)

2. Load data in table box based on CustomerId entered by user in Input Box (table T2). This value I am capturing in vCustomerId variable.

The step 1 works fine. But for step 2 it loads complete data irrespective the value entered by user.

Also, can we re-populate table T2 when user select any value in list box of above step 1

T1:

DIRECT QUERY

DIMENSION

CustomerID AS [Cust Id]

FROM Orders;

LET vCustomerId = Null;

LET vCustomerId = Input('Please Enter the Customerid', 'CustomerId Input');

NoConcatenate

T2:

SQL SELECT * FROM Orders where customerid='$(vCustomerId)';

Re: Variables with Direct Discovery

ecolomer — Sun, 04 Jun 2017 07:28:53 GMT

For load customers, you can also write:

LOAD DISTINCT

CustomerID as [Cust Id]

from Orders;

for second question, you can use variable in the graph's formulas and isn't necesary in the script.

Re: Variables with Direct Discovery

Anonymous — Sun, 04 Jun 2017 10:29:52 GMT

Sorry, forgot to mention I am using sql server as data source.

Re: Variables with Direct Discovery

ecolomer — Sun, 04 Jun 2017 10:35:27 GMT

Is the same, you can use DISTINCT with SQL SERVER also.

LOAD DISTINCT

CustomerID as [Cust Id]

SQL SELECT *

FROM ........;

Re: Variables with Direct Discovery

Anonymous — Sun, 04 Jun 2017 15:04:12 GMT

I have tried below:

T1:

SQL SELECT DISTINCT(CustomerID) AS [Cust Id] FROM NWindQlikView.dbo.Orders;

LET vCustomerId = Null;

LET vCustomerId = Input('Please Enter the Customerid', 'CustomerId Input');

T2:

DIRECT QUERY DIMENSION CustomerID

detail OrderID,OrderDate

FROM NWindQlikView.dbo.Orders where customerid='$(vCustomerId)';

The T1 is binded to ListBox and T2 is binded to Tablebox.

The T2 works fine when loaded for the first time. It loads data based on customerid entered by user.

My requirement that if user selects the value in Listbox(of T1) table the TableBox(T2) should be filled from database (as Direct Query is used).

Re: Variables with Direct Discovery

rajreddys — Mon, 22 Jun 2020 14:06:27 GMT

Hello, were you able to get this use case work. I have a similar scenario and trying to figure out options.