topic Re: Storing selection into qvd in QlikView

Storing selection into qvd

Thu, 27 Mar 2014 07:08:40 GMT

Hi,

I'm new to qlik view.I'm using records from teradata.I'm having some list boxes I need to store only the selected values from the list box into a new qlikview document on clicking the button object at the same time only the selected values should be displayed in teradata too(using select staement).Please help me out in solving this.Thanks in advance.

Re: Storing selection into qvd

PradeepReddy — Thu, 27 Mar 2014 07:15:26 GMT

We can use the bookmark option from Menu Bar or from Book Mark object, to store the current selections.

Re: Storing selection into qvd

anil2185 — Thu, 27 Mar 2014 07:16:39 GMT

What I under stand is to display specific data?

You can put the where condition in the store statement of .qvd, base on your selections.

Regards,

Re: Storing selection into qvd

Thu, 27 Mar 2014 07:24:56 GMT

To store the selected data into the qvd file and also display the same in teradata.

Ya ,but I have no idea to get the selected field and storing it to variable.

Re: Storing selection into qvd

anil2185 — Thu, 27 Mar 2014 07:30:53 GMT

Use GetCurrentSelections()

Re: Storing selection into qvd

Thu, 27 Mar 2014 07:31:26 GMT

Ya I tried,but i couldnt use the bookmark name in text box object to display the selected values.

Re: Storing selection into qvd

stevedark — Thu, 27 Mar 2014 07:37:20 GMT

To create a new QlikView document that contains only the Current Selections you need to select File \ Reduce Data \ Keep Possible Values and then save this. This should be possible in a macro via the API - but it is not something I have attempted.

As for having QlikView send a selection into TerraData I can't see how it would do this. The closest I can imagine you could get is building a SELECT statement in a Text Object that the user could copy and paste. This would need something like. You would need to concatenate a string with the SELECT part then for the where would would need a mix of GetSelectedCount and Concat to create a WHERE EXISTS IN statement.

Hope that points you in the right direction.

Steve

Re: Storing selection into qvd

Thu, 27 Mar 2014 10:01:42 GMT

I tried to save GetCurrentSelections() into a variable and using the where condition as below; But this is not turning up or storing the values as expected;

LOAD cust_id,
cust_name,
cust_type
FROM
C:\Users\387805\Documents\cust_type.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE (cust_id = '$(vS)');

LOAD *;
Store cust_type into cust_type.qvd(qvd);

Re: Storing selection into qvd

Thu, 27 Mar 2014 10:11:59 GMT

Hi ,

I wrote macro code in the Edit module like this

Sub select_field

set s = ActiveDocument.Fields("V_Id").GetSelectedValues

End Sub

----------------------------------------in Edit script

LOAD cust_id,

cust_name,

cust_type

FROM

C:\Users\<User>\Documents\cust_type.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE (cust_id = $(s));

Can you please suggest me how to pass the variable from macro to script.?

Re: Storing selection into qvd

Thu, 27 Mar 2014 10:15:48 GMT

Passing bookmark name into load/select statement is not possible. It shows error.

Re: Storing selection into qvd

stevedark — Thu, 27 Mar 2014 10:53:03 GMT

Hi there,

You don't need to have a macro, you can just place the code in the variable. In order to deal with multiple selections the code will need to be a touch more complex, something like:

=if(GetSelectedCount(cust_id) > 0,

'WHERE cust_id IN (' & chr(39) & Concat(DISTINCT cust_id, chr(39) & ',' & chr(39) ) & chr(39) & ')',

'')

If you create a variable called vWhere and then paste the code above into it, your select script would then need to be:

LOAD cust_id,

cust_name,

cust_type

FROM

C:\Users\<User>\Documents\cust_type.xlsx

(ooxml, embedded labels, table is Sheet1)

$(vWhere)

;

The variable would get a little more complex still if you have multiple fields, as you would need to decide by looking at the selected count of each whether you needed to include WHERE and AND statements.

The reduce data approach that I described above with the reduce data would probably be simpler though.

Steve

Re: Storing selection into qvd

tresB — Thu, 27 Mar 2014 10:53:55 GMT

Two points:

write WHERE (cust_id = $(s)); as WHERE cust_id = $(s); // remove extra parenthesis
getting retunred value from macro in script you have to declare a function properly, read: Re: Calling VB script macro from load script

Re: Storing selection into qvd

Tue, 01 Apr 2014 10:43:00 GMT

Hi Steve,

I done as you suggest above by using variable .

LOAD V_Id AS V,

Start as s,

End as e

FROM

C:\Users\387805\Documents\date_visit.xlsx

(ooxml, embedded labels) $(V1);

Got error like this.

And also, the selected values should be stored in qvd only after cliking the button object.

Re: Storing selection into qvd

stevedark — Tue, 01 Apr 2014 10:50:54 GMT

Check the contents of the variable - you may have a space in front of the equals sign. It is critical that the very first character is the =.

Re: Storing selection into qvd

Tue, 01 Apr 2014 11:10:53 GMT

I checked for blank space in front of equals sign but still getting the same error.One thing I want to be clear that Should I assign this code

=if(GetSelectedCount(cust_id) > 0,

'WHERE cust_id IN (' & chr(39) & Concat(DISTINCT cust_id, chr(39) & ',' & chr(39) ) & chr(39) & ')',

'')

to variable using variable overview or action (set variable) in button properties? because button has to be clicked inorder to store the current selection to the qvd file.

Re: Storing selection into qvd

stevedark — Tue, 01 Apr 2014 11:20:19 GMT

Place it in to the variable using the Variable Overview. You can then check the variable by pasting it into a text box on the desktop using this code:

=$(V1)

You should see the correct WHERE statement appear.

This solution does not need buttons or actions.

Re: Storing selection into qvd

Tue, 01 Apr 2014 11:52:04 GMT

Only if I assigned GetFieldSelection() to variable V1 , I can see the selected value through text box.

And not able to see the result of the below expression through text box. I dont have any idea about correct WHERE statement.

if(GetSelectedCount(V_Id) > 0,
'WHERE V_Id IN (' & chr(39) & Concat(DISTINCT V_Id & chr(39) & ',' & chr(39) ) & chr(39) & ')',
'')

Re: Re: Storing selection into qvd

stevedark — Tue, 01 Apr 2014 13:29:31 GMT

The syntax for the variable is correct. Please see the attached example.

You get the following when you multi select values:

Steve

Re: Storing selection into qvd

nizamsha — Tue, 01 Apr 2014 13:38:56 GMT

Hi Steve

I cant get u can u post two qvd so that i can understand clearly .what i understand in this post is if i select value in one qvw the selected value should display in another qvw kindly post a qvw

Re: Storing selection into qvd

stevedark — Tue, 01 Apr 2014 20:35:25 GMT

Hi Nizam - that's not what this is trying to achieve. The remit was to make selections in a list box and then do a reload - only pulling in the selected values.

It is possible to pass selections into other documents, if you create an Action (on a button, perhaps) to open another QlikView app you can chose to send the selection across. Obviously this depends on the two applications having the same data model.