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

[#1]

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!

[alexandros17]

Load

...

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

...

from

...

[#3]

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;

[MK_QSL]

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

[maxgro · Accepted solution]

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

[#6]

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;

[#7]

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

[#8]

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

[#9]

This worked, thanks so much!

[#10]

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