Duplicate rows discarded when reloading Excel data by using FOR-NEXT loop.

[joydipp1988]

Dear All,

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.

Thanks in advance.

Regards,

Joy

[trdandamudi · Accepted solution]

May be like this...

// Load Variables From Excel into Qliview Table

Temp_Variables:

LOAD [Variable Name],

          [Variable Definition]

FROM

(ooxml, embedded labels, table is Sheet1);

//Create Variables

For i =0 to NOOfRows('Temp_Variables') -1

  Let vVariable_Name =    Peek('Variable Name', i, 'Temp_Variables');

  Let vVariable_Expression = peek('Variable Expression', i, 'Temp_Variables');

next i

[joydipp1988]

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-

VARIABLE_DETAILS_TABLE:

LOAD

    RecNo()                as RecNo,

    [Variable Name]        as Variable_Name,

    [Variable Definition]  as Variable_Definition

FROM

[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 */

    Tab:

    LOAD

        '$(vVariableName)'        as Var_Name,

        '$(vVariableDefinition)'  as Var_Def

    AutoGenerate 1;

    SET $(vVariableName) = $(vVariableDefinition);

NEXT i

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.

Regards,

Joy