Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Next I
Try to use let expression locvarname1 instead of i
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?
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.
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
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
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