Need help with qlikview design approach

[#1]

I am really having a difficult time here and are hoping for some general ideas on how to approach this.

I am tasked with converting the functionality in an excel workbook into qlikview. The data is being loaded and processed in SQL Server, and then loaded into qlikview. Think of this data as being like a customer name, acount number, country, currency, and balance, with some other details. The users want to be able to input various amounts such as rates and so forth, so that new balances can be calculated based on what they are entering, so I need a way to provide a couple of input fields per row they are working with. And depeneding on the number of accounts the customer has, they might be working with several or up to 15 accounts at once.

So this isnt just a straight up report, but one where I have to be able to re-calculate balances on the fly based on what the users enter.

My initial thought was to use input boxes, but that means I could only have a set amount built on the sheet at design time. I set about doing this, using list boxes to select the name, account, currencies, etc, and its just really messy, especially since my data has some 300,000 rows, and lots of customers.

So then I leaned to trying to create a table, and embedding the input columns in it, and using expressions to re-calculate the values based on those inputs, and thought that would work, however, when I define the inputfield in the load script, setting it to 0, when the data loads into the table, and using inputsum(NewRate) for example, the column gets pre-populated with what seems is the count of how many times that customer might appear in my initial load, meaning I could have the same customer appear 10 times if he has 10 different accounts, so therefore the value of that column, instead of being 0, shows 10. I can't have this. I need a default of null or 0.

So imagine a grid or something with: Customer Name, Account, Country, Currency, Balance, Rate, New Balance.

The Rate would be the input field, and New Balance would be the calculated value of Balance * Rate. Seems simple enough, but I am at a loss as to getting around the value being populated in the inputsum field. And if I go outside the table approach, then it seems I have ot create a bunch of list boxes, input boxes, and text fields just to handle one account selection, let alone several.

Any ideas? BTW after this, I have to show a report containing the results of all this on another sheet.... but first things first.

[marcus_sommer]

Generally it is possible to create these functionalities with inputfields but you need an appropriate datamodel and maybe some additionally objects, for example tableboxes for user-inputs and charts to show the results. This needs some effort to build these app and for the user to learn to handle with it. QlikView isn't really designed for such user-inputs.

An alternatively could be to use extensions like these: SQL Writeback from QlikView extension object.

- Marcus

[#3]

Hi Marcus, thanks, yeah I am wondering if Qlikview isnt the right tool for this.

I did however, find a semi-solution for the default value in the input field. Rather than creating a default value of 0 during the load, I changed the source SQL table by adding an Input_1 column that is just null, and then in the load saying Input_1 as <inputfieldname>. When the table loads, I get my 0 value I want, however, even after putting Inputsum(inputfieldname), I can modify the 0, but it wont stay. After entering a new value and hitting enter, it just goes back to 0. What the heck.

[#4]

Wow, I just discovered I can work around my issue by bringing in a null column, assigning the first inputfield to that, then a second being assigned to that ame column, and then a third defaulted to 0, which will actually come in as 0 as I want. I can manipulate the column just as I wanted.

INPUTFIELD Balance.Dummy1,  //Dont ask

Balance.Dummy2,   //Dont ask

Balance.InputLocalBalance,

Balance.InputAdditionalIncrementalRate;

;

Balance:

SQL SELECT blablabla,

INPUT_1 AS Dummy1,

INPUT_1 AS Dummy2,

0 AS InputLocalBalance

FROM SQLTable

So weird.