Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Importing data that contains qlikview reserved word

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

12 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

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

Not applicable
Author

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

Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

It means your variable is not set properly.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

I set it right above? What is wrong with that operation?

Thanks,

Zach

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

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....

Not applicable
Author

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