Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
hannahnoelle797
Contributor II
Contributor II

Reading a List from Excel and Setting as a Variable Value

I am trying to read a list of values from an excel sheet where the name of the variable is in column A and the value of the variable is column B

Name Value
ListA ['abc','def','ghi','jkl']
ListB ['mno','pqr','stu']

 

I am loading the data in from the file and then setting up my variables as such

 

LET vNROWS = NoOfRows('Variable');

FOR i=0 to (vNROWS-1)
LET vVariable_Name = Peek('Variable_Name', i, 'Variable');
[$(vVariable_Name)] = Peek('Variable_Value', i, 'Variable');
next

 

So when it finishes looping through I expect to have two list variables: $(ListA) and $(ListB) with the values

$(ListA) = ['abc','def','ghi','jkl']

$(ListB) = ['mno','pqr','stu']

I then want to use the value in an if statement of another data load

 

LOAD
 Letters,
 if(match(Letters, $(ListA)), 'List A', 
  if(match(Letter, $(ListB)), 'List B', 'No List')) as List_Title
FROM other_file.QVD (qvd);

 

However, I always get an error on one of the commas or apostrophes, etc. 

Is there a way to read a string that is a list and convert it to a list value so that it can be used later on in my code?

Labels (1)
1 Solution

Accepted Solutions
hannahnoelle797
Contributor II
Contributor II
Author

Update: What I have above works just fine. Another piece of my load statement wasn't correct which was causing it to appear to not work. 

View solution in original post

2 Replies
KGalloway
Creator II
Creator II

This seemed to work for me:

Variable:
LOAD
    Name as Variable_Name,
    Value2 as Variable_Value
FROM [lib://MyFolder/ScriptTesting.xlsx]
(ooxml, embedded labels, table is Sheet1);
 
 
LET vNROWS = NoOfRows('Variable');
 
FOR i=0 to (vNROWS-1)
LET vVariable_Name = Peek('Variable_Name', i, 'Variable');
v$(vVariable_Name) = Peek('Variable_Value', i, 'Variable');
next i;
 
table:
load
Letters,
  if(wildmatch('$(vListA)', '*|'&Letters&'|*'), 'List A', 
  if(wildmatch('$(vListB)', '*|'&Letters&'|*'), 'List B', 'No List')) as List_Title
inline [
Letters
abc
def
ghi
kjl
jkl
mno
pqr
stu
abd
ab
stuu
]
;
 
Notably, I changed the list variable to use Value2 instead of Value. Value 2 used the pipe character, |, as a separator and a leading and ending character so that wildmatch only finds exact strings.

KGalloway_0-1696619049639.png

 

hannahnoelle797
Contributor II
Contributor II
Author

Update: What I have above works just fine. Another piece of my load statement wasn't correct which was causing it to appear to not work.