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
Hi,
The variable declaration seems to be right.
Check if your Data table has got rows.
Also check if role is null in your data or not.
Regards,
Kaushik Solanki
Try this.
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
Regards,
Kaushik Solanki
What if you do this
DataDetail:
LOAD $(vAct) as [Account ID],
$(vStart) + RecNo() - 1 as PeriodDate,
Year($(vStart)) as Year,
Month($(vStart)) as Month,
'$(vRole)' as [Role]
AutoGenerate $(vDays);
I tried that but QV does not pick up the macro inside the single quotes. Also tried [$(vRole)]. Because it doesn't pick it up I get this error on Account ID.
Syntax error
Script Error Log:
DataDetail:
LOAD as >>>>>>[Account ID]<<<<<<,
+ RecNo() - 1 as PeriodDate,
Year() as Year,
Month() as Month,
'' as [Role] <- DOES NOT PICK ANYTHING UP INSIDE QUOTES
AutoGenerate
I wish there was a String() method similar to Date() and Num() .
Thanks for the reply,
Zach
I tried that but QV does not pick up the macro inside the single quotes. Also tried [$(vRole)]. Because it doesn't pick it up I get this error on Account ID.
Syntax error
Script Error Log:
DataDetail:
LOAD as >>>>>>[Account ID]<<<<<<,
+ RecNo() - 1 as PeriodDate,
Year() as Year,
Month() as Month,
'' as [Role] <- DOES NOT PICK ANYTHING UP INSIDE QUOTES
AutoGenerate
I wish there was a String() method similar to Date() and Num() .
Thanks for the reply,
Zach
Hi,
It means your variable is not set properly.
Regards,
Kaushik Solanki
I set it right above? What is wrong with that operation?
Thanks,
Zach
Hi,
The variable declaration seems to be right.
Check if your Data table has got rows.
Also check if role is null in your data or not.
Regards,
Kaushik Solanki
What exactly are you trying to do here? There might be an easier way to do this instead of doing this in a loop and by looking at one cell at a time....
I am looking at different contracts (Account ID) that have different start and end dates. These contracts require a certain amount of different roles. I have a very similar script that reads an excel file of the requirements but I do not have this problem because it is reading a number instead of a role name. In QV the count function is executed on the field Role where role = 'Operations and Helpdesk', 'Back end Developer', etc. I need the data broken down from just start and end dates to daily data is I guess my goal here.
Thanks,
Zach