topic Re: How to create a new field in script and then pull it into a chart in QlikView

How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:09:21 GMT

I am still fairly new to QlikView and need help with a simple process.

I have a load script and want to create new fields within the script from the originally loaded fields that I can then use in various charts. I want to simplify the script and need new fields that would be very complex.

I attached the basic script. From this I want to get the following three new fields:

Overtime_Pay = Ovt_Pay_Rate*Ovt_Hrs,

Regular_Pay = Pay_Rate*Regular_Hrs,

Total_Pay = Overtime_Pay + Regular_Pay

How do I create these three new fields and make them available to pull into different charts/graphs.

Thank you in advance!

Re: How to create a new field in script and then pull it into a chart

alexandros17 — Fri, 25 Jul 2014 15:13:44 GMT

Load

...

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,
Pay_Rate*Regular_Hrs as Regular_Pay,
Overtime_Pay + Regular_Pay as Total_Pay,

...

from

...

Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:15:06 GMT

Hi there you can do a pre-load.

Load *, Overtime_Pay + Regular_Pay as Total_Pay ;

Load *,

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,

Pay_Rate*Regular_Hrs as Regular_Pay ;

Load Your script

From Table;

Re: How to create a new field in script and then pull it into a chart

MK_QSL — Fri, 25 Jul 2014 15:15:40 GMT

Load

Overtime_Pay + Regular_Pay as Total_Pay;

Load

Ovt_Pay_Rate,

Ovt_Hrs,

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,

Pay_Rate,

Regular_Hrs,

Pay_Rate*Regular_Hrs as Regular_Pay,

From TableName....

Re: How to create a new field in script and then pull it into a chart

maxgro — Fri, 25 Jul 2014 15:19:20 GMT

My_Data:

LOAD

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,

Pay_Rate*Regular_Hrs as Regular_Pay,

Ovt_Pay_Rate*Ovt_Hrs + Pay_Rate*Regular_Hrs as Total_Pay;

LOAD
Pay_End,
Year(Pay_End) AS Year,
Month (Pay_End) AS Month,
Day(Pay_End) AS Day,
MonthName(Pay_End) AS MonthName,
Job_Code,
Position_Name,
Tot_Hrs,
Ovt_Hrs,
Tot_Hrs - Ovt_Hrs as Regular_Hrs,
if(Job_Code = '34' or Job_Code = '30', 'PS1',
if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', 'PS2',
if(Job_Code = '88' or Job_Code = '36', 'PM1',
if(Job_Code = '37', 'PM2',)))) as JOB_GROUP,

if(Job_Code = '34' or Job_Code = '30', '9.00',
if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', '9.50',
if(Job_Code = '88' or Job_Code = '36', '10.00',
if(Job_Code = '37', '10.50',)))) as Pay_Rate,

if(Job_Code = '34' or Job_Code = '30', '13.50',
if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', '14.25',
if(Job_Code = '88' or Job_Code = '36', '15.00',
if(Job_Code = '37', '15.75')))) as Ovt_Pay_Rate,

if(Job_Code = '34' or Job_Code = '30', 13.50*(Tot_Hrs-Ovt_Hrs),
if(Job_Code = '35' or Job_Code = '90'or Job_Code = '91', 14.25*(Tot_Hrs-Ovt_Hrs),
if(Job_Code = '88' or Job_Code = '36', 15.00*(Tot_Hrs-Ovt_Hrs),
if(Job_Code = '37', 15.75*(Tot_Hrs-Ovt_Hrs))))) as Reg_Pay,

if(Job_Code = '34' or Job_Code = '30' , 13.50*Ovt_Hrs,
if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91' , 14.25*Ovt_Hrs,
if(Job_Code = '88' or Job_Code = '36' , 15.00*Ovt_Hrs,
if(Job_Code = '37' , 15.75*Ovt_Hrs)))) as Ovt_Pay

FROM

My_Data.qvd
(qvd);

Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:19:28 GMT

You need to do something like this:

Use the table that you already create and after that put your formulas

My_Data:

LOAD

Pay_End,

Year(Pay_End) AS Year,

Month (Pay_End) AS Month,

Day(Pay_End) AS Day,

MonthName(Pay_End) AS MonthName,

Job_Code,

Position_Name,

Tot_Hrs,

Ovt_Hrs,

Tot_Hrs - Ovt_Hrs as Regular_Hrs,

if(Job_Code = '34' or Job_Code = '30', 'PS1',

if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', 'PS2',

if(Job_Code = '88' or Job_Code = '36', 'PM1',

if(Job_Code = '37', 'PM2',)))) as JOB_GROUP,

if(Job_Code = '34' or Job_Code = '30', '9.00',

if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', '9.50',

if(Job_Code = '88' or Job_Code = '36', '10.00',

if(Job_Code = '37', '10.50',)))) as Pay_Rate,

if(Job_Code = '34' or Job_Code = '30', '13.50',

if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91', '14.25',

if(Job_Code = '88' or Job_Code = '36', '15.00',

if(Job_Code = '37', '15.75')))) as Ovt_Pay_Rate,

if(Job_Code = '34' or Job_Code = '30', 13.50*(Tot_Hrs-Ovt_Hrs),

if(Job_Code = '35' or Job_Code = '90'or Job_Code = '91', 14.25*(Tot_Hrs-Ovt_Hrs),

if(Job_Code = '88' or Job_Code = '36', 15.00*(Tot_Hrs-Ovt_Hrs),

if(Job_Code = '37', 15.75*(Tot_Hrs-Ovt_Hrs))))) as Reg_Pay,

if(Job_Code = '34' or Job_Code = '30' , 13.50*Ovt_Hrs,

if(Job_Code = '35' or Job_Code = '90' or Job_Code = '91' , 14.25*Ovt_Hrs,

if(Job_Code = '88' or Job_Code = '36' , 15.00*Ovt_Hrs,

if(Job_Code = '37' , 15.75*Ovt_Hrs)))) as Ovt_Pay

FROM

My_Data.qvd

(qvd);

NO CONCATENATE

New_my_data:

LOAD

Pay_End,

Year,

Month,

Day,

MonthName,

Job_Code,

Position_Name,

Tot_Hrs,

Ovt_Hrs,

Regular_Hrs,

JOB_GROUP,

Ovt_Pay_Rate,

as Reg_Pay,

as Ovt_Pay,

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,

Pay_Rate*Regular_Hrs as Regular_Pay,

Overtime_Pay + Regular_Pay as Total_Pay

RESIDENT My_Data;

drop table My_Data;

Re: Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:50:57 GMT

I tried this (as it seemed the most direct way to resolve this), but I get the following error:

Table not found
New_My_Data:
LOAD

Pay_End,
Year,
Month,
Day,
MonthName,
Job_Code,
Position_Name,
Tot_Hrs,
Ovt_Hrs,
Regular_Hrs,
Pay_Rate,
Ovt_Pay_Rate,
JOB_GROUP,

Ovt_Pay_Rate*Ovt_Hrs as Overtime_Pay,
Pay_Rate*Regular_Hrs as Regular_Pay,
Overtime_Pay + Regular_Pay as Total_Pay

RESIDENT New_Kronos_Data

Then I get this error message:

Table not found

DROP TABLES statement

Re: Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:53:59 GMT

RESIDENT New_Kronos_Data

New_Kronos_Data has to be the name of the table that you already loaded into qlikview, the table that has all the fields, is very important that name check uppers and lower letters in the name

Re: Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 15:54:39 GMT

This worked, thanks so much!

Re: Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 16:19:16 GMT

Sorry, the name of the table did match, I just accidentally changed the name here. Still same issue.

Re: Re: How to create a new field in script and then pull it into a chart

Fri, 25 Jul 2014 17:31:17 GMT

Paste the script here and I help you