Advanced Input Box Data Capture

[andyquirin]

Hi QV Community,

I've been asked to convert a tool from Excel into QlikView and have a concern about one of the pieces of functionality.

The tool allows a user to paste in a list of SKUs and matching Quantities, and the tool outputs what the total costs, profits, data would be matching those inputs. In the past, I've built dashboards that allow me to paste in a list of SKUs or other values and been fine, but I'm concerned about trying to input multiple values at a time (especially if one is quantity which probably only be used in a calculation and wouldn't be in the data. Is there a clean way to do this in QV? Maybe a way to build an array or matrix off of a data input?

Here's what the input box looks like:

Thanks for your help!!!

Andy

[JonnyPoole]

Someone wrote an extension to do this. You can see an example under 'trades and positions' tab here:

http://us-d.demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FAsset%20Management.qvw&host=demo11

You can copy/paste a list of values into this kind of input box (extension) .

As an example, try copying /pasting these 3 values in there.

0AKS38833

0TE623663

1OJ418610

[andyquirin]

Hi Jonathan,

Thanks for the help! I think this is a step in the right direction.

The part I'm trying solve for is being able to paste a list with two columns (a product and a quantity) and have them both input and relate.

Any idea on how to capture multiple related values in an input box?

Best,

Andy

[JonnyPoole]

Hi Andy, if Quantity (or one of the fields) is not likely to be in the data model at all, yet you need to use the new values to do some calculations etc... then you are probably looking at giving the user the ability to augment the data model to bring in that extra data rather than try to capture an unknown number list of values using an unknown number of variables.

I don't know of a great way to provision each user to integrate new data fields into the app in a QlikView web deployment but i suppose it fairly easy for a set of desktop users. What is the deployment going to look like ?

That being said, what i have mentioned is pretty easy in the new product Qlik Sense where you can drag and drop a list of values stored in a file (say CSV or XLS) right into the app and have it augment the data model on the fly.  There are still some additional issues to think through but it could be a new start.

Sorry i don't have anything better for you in QlikView.

[ashfaq_haseeb]

Hi,

You have to create two input boxes for the same.

have a look at the attached application.

Regards

ASHFAQ

[andyquirin]

Hi Jonathan,

Thanks for the quick follow up - I appreciate your help and collaboration.

What I'm trying to achieve is a simple calculator of sorts. The dashboard will have a list of product IDs with associated costs:

Product ID --- Cost

1001               $5

1002               $10

1003               $15

1004               $40

1005               $80

...

There will be thousands of Products in the aforementioned table. I want the user to be able to paste a list of Product IDs and Quantities into an input box like this:

Product ID --- Quantity

1002               3

1004               15

1003               6

I then want the output to display a table with the selected Product IDs, their quantities inputted by the user, the cost, multiply the cost times the quantity to see the final price:

Product ID --- Quantity --- Cost ---- Final Price

1002               3                   $10               $30

1003               6                   $15               $90

1004               15                 $40               $600

The trick is being able to paste a matrix of information into an input box rather than just a single list of values.

Are you saying this can be done in Qlik Sense? Does this example make more sense?

Thanks again for your help.

Andy

[andyquirin]

Hi Ashfaq,

Thanks for the follow up and sending over the example. I see in the application a space to create a single input, but am still confused as how to input a matrix of information with two fields at a time (see example above).

What are you thoughts - did I miss something in the example you sent? I appreciate your help.

Best,

Andy

[Anonymous · Accepted solution]

see attached. there may be an easier way but here is one way with limitations of course and plenty of room for improvement

[JonnyPoole]

Andy - your explanation is excellent and intentions are clear. You are trying to create a user filtered display of Product IDs .  You are also trying to add a new field (quantity) and use this to create a new calculated field using existing fields in the data model.  This feels like a 'what if' scenario where you are trying to give users the ability explore pricing scenarios of selling certain quantities of certain products.  The users need to add the quantities for selected products. Makes sense to me.

.

Trying to do this with Input Boxes is a little challenging. I don't know of a really great way to do it that would be seemless for a user. I don't know of an existing extension that would help, although that method is (web developer skills) is always there.

Another way is to give users the ability to JOIN in a new table that contains Product IDs and Quantities. The chart in the app would Product IDs from the file that the users join in (call it File Product IDs) and would display Quantities.  The chart would only render once the data model has been augmented with the new file.

This is probably  easier to do in Qlik Sense because in Sense users can litteraly drag and drop a CSV or XLS file into the application , select 'add data', and hit load.   The file would have to have the right field names that are expected , but the app could provide text to guide the user. They can't really do that in QlikView so the solution isn't as seemless so i would only recommend using Sense for this option. Sense doesn't have all the same features as QlikView and does require 64 bit OS (at least for desktop). 

[andyquirin]

Hi Jsaradhi,

This is really neat! It looks like it might be able to solve what we're looking for.

My concern is around scalability. Do you think it would be able to be expanded to take up to ~30 inputs?

Any idea what the formula would be for the 'Units' and 'Total Cost' buckets would be?

Andy