topic Re: Importing data that contains qlikview reserved word in QlikView

Importing data that contains qlikview reserved word

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Importing data that contains qlikview reserved word

kaushiknsolanki — Wed, 09 Aug 2017 17:52:20 GMT

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

Regards,

Kaushik Solanki

Re: Importing data that contains qlikview reserved word

sunny_talwar — Wed, 09 Aug 2017 17:54:06 GMT

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

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 17:59:57 GMT

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

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 18:00:13 GMT

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

Re: Importing data that contains qlikview reserved word

kaushiknsolanki — Wed, 09 Aug 2017 18:02:43 GMT

Hi,

It means your variable is not set properly.

Regards,

Kaushik Solanki

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 18:05:50 GMT

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

Thanks,

Zach

Re: Importing data that contains qlikview reserved word

kaushiknsolanki — Wed, 09 Aug 2017 18:07:50 GMT

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

Re: Importing data that contains qlikview reserved word

sunny_talwar — Wed, 09 Aug 2017 18:08:34 GMT

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

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 18:13:43 GMT

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

Re: Importing data that contains qlikview reserved word

sunny_talwar — Wed, 09 Aug 2017 18:17:06 GMT

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

Loops in the Script‌

Or interval Match

IntervalMatch

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 18:43:13 GMT

Thank you for your suggestions.

Re: Importing data that contains qlikview reserved word

Wed, 09 Aug 2017 18:44:16 GMT

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.