Duplicate rows discarded when reloading Excel data by using FOR-NEXT loop.
I'm facing an issue regarding excel data reload into QlikView. My problem is depicted below-
1) Input Data:
I have two fields in my excel Variable Name, Variable Definition. I want to load them from QV script. All total 20 variables are there and some variables are sharing same definition.
2) Problem facing:
I'm reloading the excel table line by line by using FOR-NEXT loop. After reload completes, for some rows Variable Definition is coming blank. After researching the matter by using step by step debugging, I found out that for some Variable Name, same Variable Definition is present. So when this Variable Definitions are fetched for the first time it gets loaded fine. But for the second occurrence of the Variable Definition, it gets discarded.
I'm attaching the excel table (Sheet1) and the script file. Please look into the matter. You can get a better understanding if you reload the script in debug mode, step by step. At row no 10 the problem occurring for the first time. Also in the sheet Duplicate Values I'm showing the duplicate values which are marked in pink.
Re: Duplicate rows discarded when reloading Excel data by using FOR-NEXT loop.
Thanks Thirumala for quick reply. Actually your answer is kind of eye opener. In my app I used FieldValue function but it din't work out because FieldValue function works only on the distinct values of a field. Peek function visits all the rows one by one irrespective of duplicate values.
My final code looks something like this-
RecNo() as RecNo,
[Variable Name] as Variable_Name,
[Variable Definition] as Variable_Definition
[Variables & Definitions.xlsx]
(ooxml, embedded labels, table is Sheet1);
FOR i=0 to NoOfRows('VARIABLE_DETAILS_TABLE')-1
LET vVariableName = Peek('Variable_Name',$(i),'VARIABLE_DETAILS_TABLE');
LET vVariableDefinition = '=' & Peek('Variable_Definition',$(i),'VARIABLE_DETAILS_TABLE');
/* This table is created to check whether all variables created properly or not. This is only for checking purpose and not needed in final deployment */
'$(vVariableName)' as Var_Name,
'$(vVariableDefinition)' as Var_Def
SET $(vVariableName) = $(vVariableDefinition);
So the conclusion is when we need to visit every row of the data irrespective of the duplicate values, we need to use Peek() but in case of situations where we need only the distinct set of values then FieldValue() is effective.