Qlikview read Excel issues (loop)

[shayraber]

Hi all,

I have an Excel spreadsheet with 3 columns:

ID

Text1

Text2

assuming the following records:

ID     Text1     Text2

rec1     Hello     Hola

rec2     bye     adiós

rec3     morning     Mañana

etc.

i want to create (via loop) a list of the following variables:

rec1_1 = Hello

rec1_2 = Hola

rec2_1 = bye

rec2_2 = adiós

rec3_1 = morning

rec3_2 = Mañana

etc.

i created the following script and it works partly... Only that the QV skips few of the excel records...don't know why

input:

LOAD ID,

     Text1,

     Text2

FROM (ooxml, embedded labels, table is test);

LET number = NoOfRows('input');

FOR i = 0 TO number          

   LET LocVarName1        = FIELDVALUE( 'ID',i) & '_1'; 

   LET LocVarName2        = FIELDVALUE( 'ID',i) & '_2';   

   LET $(LocVarName1)   = FIELDVALUE( 'Text1',i);        

   LET $(LocVarName2)   = FIELDVALUE( 'Text2',i);        

NEXT

[Anil_Babu_Samineni]

Next I

Try to use let expression locvarname1 instead of i

[shayraber]

Hi Anil,

not sure what you meant, can you please explain?

another thing, i noticed that the first record that didn't caused the proper creation of variables was record which its Text2 value was the same as previously record Text2 field.

Variable rec138_2 was the first one that didn't create well where Text2 at line #138 was: Schichten (and what was found out is that Text2 at line #120 also was: Schichten).

changing the value to Schichten something solved it so it had something to do with the value itself but i can find the reason why... the value is targeted to be the variable value. variables CAN have the same value, aren't they?

[Gysbert_Wassenaar]

Personally I'd start at 1 instead of 0, but that shouldn't matter since the first iteration simply wouldn't create anything. I'd also make sure to use dollar-expansion so $(number) instead of number and $(i) instead of i inside the for loop. But that also shouldn't matter. So what's left is your excel file. And since you didn't post that I have no idea what could be wrong with that.

[shayraber]

Hi Gysbert,

as you suggested, i've started the loop from 1 and it didn't matter. as I wrote above, it look like an issue with the cell value itself (don't know why, but is a fact).

i've changed the value - and things worked. the value is a duplication of a previous value (appearantly) but don't see any reason why it matters

[Gysbert_Wassenaar · Accepted solution]

FieldValue gets the values from the symbol table, not from the input table. The symbol table has only records for the unique values, not duplicates. You should use the peek function to retrieve the values from the input table.

input:

LOAD ID,

    Text1,

    Text2

FROM (ooxml, embedded labels, table is test);

LET number = NoOfRows('input');

FOR i = 0 TO number -1       

  LET LocVarName1        = Peek( 'ID',$(i),'input' & '_1';

  LET LocVarName2        = Peek( 'ID',$(i),'input' & '_2';

  LET $(LocVarName1)  =Peek( 'Text1',$(i),'input');      

  LET $(LocVarName2)  =Peek( 'Text2',$(i),'input');

NEXT