Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am using the following script to import data from an excel table and then fill in the missing data. I keep getting an error when importing the field [Role] as it contains strings such as "Operations and Help desk" that Operations field cannot be found. I am pretty sure it is happening because QV is reading the "and" as a reserved word. Any help would be very appreciated. Thank you.
Zach
Data:
LOAD [Account ID],
num(Date([Start Date], 'M/D/YYYY')) as StartDate,
num(Date([End Date], 'M/D/YYYY')) as EndDate,
num(Date([End Date])) - num(Date([Start Date])) + 1 as Days,
[Role]
FROM
(ooxml, embedded labels, table is Sheet1);
for i = 0 to NoOfRows('Data')-1
let vAct = Peek('Account ID', $(i), 'Data');
let vDays = Peek('Days', $(i), 'Data');
let vStart = Peek('StartDate', $(i), 'Data');
let vDays = Peek('Days', $(i), 'Data');
let vRole = Peek('Role', $(i), 'Data');
DataDetail:
LOAD $(vAct) as [Account ID],
$(vStart) + RecNo() - 1 as PeriodDate,
Year($(vStart)) as Year,
Month($(vStart)) as Month,
$(vRole) as [Role] <- PROBLEM OCCURS HERE
AutoGenerate $(vDays);
next
It seems that all you are trying to do is to create all dates between start and end date? If that is what you want, may be try using
While Loop
Or interval Match
Thank you for your suggestions.
Thank you for your help. There were a few rows 'cut' from the end of the excel sheet and not actually deleted. This caused the null problem error.