Inputfield doesn't work in straight table

[amber2000]

Hello,

I hope this time someone can help me please, I'm desperate

Inserting an inputfield in a table box works but when I try to use the inputfield in a straight table the field is static and can't be manipulated.

A tablebox is not sufficient enough because I want to be able to create Expressions.

Can anyone help please????

I've prepared a small QVW with a little bit of data to make my question a bit more visual.

Kind regards,

Monique

[amber2000]

Hello Richard,

Sorry but I'm new to qlikview and I haven't worked with macro's yet.

Is it possible you can put an example on how I can do this in the qvw I added please?

Thanks to all,

Kind regards,

Monique

[richard_pearce6]

It's not for the faint hearted and macros are generally avoided in QV.

I'll pop something together for you tomorrow when I'm back in the office

Richard

[richard_pearce6]

Morning Monique,

I've put this together for you, hopefully it helps.

Ctrl + M will get you to the VBA editor, or you can follow the actions on the buttons

This will allow you to update "True" or "False" to the input fields but you can only do it (on this example) one row at a time. I can be extended to apply to all selected rows although the code becomes more complex to follow.

Now that you're applying text to the field you can't use =inputsum() expression in the chart

=only() or using the field as a dimension will display the data but it can't be edited free text in that way

I hope this helps

Richard

QlikCentral.Com

[amber2000]

Thanks a lot Richard, the example is very clear.

I'm stuck with these inputfields

if I use a table box I can't do any manipulation at all like expressions and so on.

If I use Straight table then I can't insert text.

(your solution is good for fields that require Y/N or 0/1 but if I need to write text It's not possible in the straight table)

I'm getting so frustrated.

Many gratitudes to all who are trying to solve this puzzle for me, I realy appreciate it.

Monique

[amber2000]

Hello Richard,

Can you show me how I can add a rowno automaticly as long as there is data in the file?

In the previous example I had 7 rows.

I would like to have a function that adds automaticly a rowno for each record until it reaches the last row and then stop.

If possible could you add such an example in the qvw where you added the other macro please?

Kind regards,

Monique

[amber2000]

Hi Richard,

My question about the rowno() isn't an issue annymore, I have a unique ID that I named RowNo to implement your macro example.

But when I try to set a value with the textbox true or false the qvw stays in the macro edit module and does nothing if I leave the editor.

Can you tell me if I'm doing something wrong in the script? In the report I've made an exact copy of the buttons, textboxes and so on.

The only thing I can't figure out in you report example is the use of the field 1 with value 1, for what does this stand?

Example of the script:

INPUTFIELD Colli_Given, InputField, Wikkeling, Stapeling,Emptys_Ref, Empty_Given, Empty_To_Give, Damage;

Date(DayStart([Ass_Date])) As [Date_Picking],
Year([Ass_Date]) As [Date_Picking Year],
Month([Ass_Date]) As [Date_Picking Month],
Week([Ass_Date]) As [Date_Picking Week],
WeekDay([Ass_Date]) As [Date_Picking Day],
Date(Monthstart([Ass_Date]),'MM-YYYY') As [Date_Picking MonthYear];

SET vRowNumber ='=only(RowNo)';
LET vUpdate_TrueFalse = '';

Palletcontrole:
LOAD
   [SK_Productivity_Colli] AS [RowNo],
    DATE#([DT_Start_Dates], 'YYYYMMDD') AS [Ass_Date],
    [FK_Employee] AS [Employee_ID],
    [FK_Shop] AS [Customer_ID],
    [FK_Article] AS [Article_ID],
    [FK_Picking_Method] AS [FlowType_ID],
    [BK_Pallet_Cd] AS [Ass_Pallet],
    [BK_Invoice_Cd] AS [Ass_Invoice],
    [BK_Assignment_Cd] AS [Ass_Assignment],
    [BK_License_Plate_Cd] AS [Ass_LP],
    [M_Nr_of_Colli] AS [Ass_Colli],
     '0' AS [Colli_Given],
     '0' AS [InputField],
     'ok' AS [Wikkeling],
     'ok' AS [Stapeling],
     '0' AS [Emptys_Ref],
     '0' AS [Empty_To_Give],
     '0' AS [Empty_Given],
     '0' AS [Damage];

//SET vRowNumber ='=only(RowNo)';
//LET vUpdate_TrueFalse = '';

SQL SELECT Top 200000
   [SK_Productivity_Colli],
    [DT_Start_Dates],
    [FK_Employee],
    [FK_Shop],
    [FK_Article],
    [FK_Picking_Method],
    [BK_Pallet_Cd],
    [BK_Invoice_Cd],
    [BK_Assignment_Cd],
    [BK_License_Plate_Cd],
    [M_Nr_of_Colli]
FROM DWH.dbo."F_Productivity_Colli"
order by DT_Start_Dates desc;

Kind regards,

Monique

[richard_pearce6]

Hi Monique, sorry for the delay in responding to your questions.

First the easy answer: I use an expression field 1 with value 1 to expand the straight table. Because its a chart object and I made the inputfield a dimension the chart needs an expression to calculate. I'd expect your chart would have other expressions in the final version so mine can be removed. If not you can always hide it in the presentation tab.

Second: qvw stays in the macro edit module and does nothing if I leave the editor. This would be because there's a syntax error somewhere. I am going to guess its maybe the Row Number value

Try replacing your code:

[SK_Productivity_Colli] AS [RowNo],

to

RowNo()                        as [RowNo],

This will give you a integer starting from zero.

If this doesn't work try writing the command Exit Sub after the message boxes:

msgbox vRowNumber.getcontent.string

msgbox vUpdate_TrueFalse.getcontent.string

Exit sub

Exit Sub should halt the macro before writing to the InputField and before it does you will see the value of the two variables pop up on screen. It's going to be the first one you're interested in, ensure this relates to the line number (or RowNo) of the records you're wishing to update. The second variable should say either 'True' or 'False' depending on which button you pressed.

If all else fails comment out any fields not required for this test and do a limited load (say ten records) in the script debugger,  post back to me on here and I'll have a look for you.

Richard

QlikCentral.com

[amber2000]

Hi Richard,

Thank you so much for helping me getting this sorted out.

Unfortionaly it isn't working, after 2 days of strugling I turn back to you, maybe you can see what I'm doing wrong.

I've noticed that RowNo() is not filling up (maybe one of the reasons it's failing)

I'll send you my QVW file (this is the original file with the original script and your macro imbedded), If you do a reload it wont work because the data is on a outside server.

I've send the data that you see now in the qvw to excel and I'll provide the excel sheet to test more easy.

But I needed to provide the actual script I work with because it's different from a small excel load.

I really appreciate your efforts 😉

Monique

[richard_pearce6]

Hi Monique,

The actions button was calling this code:

Sub ResetInputField

' Reset the InputField

set fld = ActiveDocument.Fields("InputField")

fld.ResetInputFieldValues 0, 0 ' 0 = All values reset, 1 = Reset Possible value, 2 = Reset single value

End SUB

This resets the InpuField to the original load values. The name of the field has to be defined and in my example I called it (originally) InputField which I've highlighted in red. As your data model does not have a field called InputField it got confused and that's why the code didn't work and you were taken to the script editor.

You'll need to update both subroutines with the Field Name you require.

RowNo are all set to zero which is strange as I can't see what's immediately wrong with the code. Thinking on it should be RowNo()-1 as that gives you results starting at zero rather than one. The only thing I can suggest is to reload the table as resident and apply the rowno()-1 field at that point. Its maybe something to do with the SQL although I honestly don't know why its not working as it should be independent from the SQL.

Richard

QlikCentral.Com