topic Qlikview read Excel issues (loop) in QlikView

Qlikview read Excel issues (loop)

shayraber — Sun, 24 Jul 2016 11:34:41 GMT

Hi all,

I have an Excel spreadsheet with 3 columns:

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

Re: Qlikview read Excel issues (loop)

Anil_Babu_Samineni — Sun, 24 Jul 2016 12:22:58 GMT

Next I

Try to use let expression locvarname1 instead of i

Re: Qlikview read Excel issues (loop)

shayraber — Sun, 24 Jul 2016 12:44:29 GMT

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?

Re: Qlikview read Excel issues (loop)

Gysbert_Wassenaar — Sun, 24 Jul 2016 12:44:37 GMT

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.

Re: Qlikview read Excel issues (loop)

shayraber — Sun, 24 Jul 2016 12:58:51 GMT

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

Re: Qlikview read Excel issues (loop)

Gysbert_Wassenaar — Sun, 24 Jul 2016 13:04:02 GMT

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');