Adding a calculated column to the data

[#1]

Hello,

I would like to add new column to my data. I have some timesheets with hours and article number, and i want to calculate the sum of each row in my table(hours * article_price = sum). I have 2 spreadsheets that I get my data from.

The first is the pricelist:

ActTyp	Price_2013
91011	1007
91012	1007
91013	1007
91014	1007
91015	1007
91016	1007
91021	936
91022	936
91023	936
91024	936
91025	936
91026	936

The second is the time sheet:

Pers.No.	ActTyp	Date	Number
00005959	91041	15.02.2013	7,500
00005959	91046	15.02.2013	0,500
00005959	91041	14.02.2013	7,500
00005959	91046	14.02.2013	0,500
00005959	91041	13.02.2013	6,000
00005959	91046	13.02.2013	1,500
00005959	91046	13.02.2013	0,500
00005959	91041	12.02.2013	7,500
00005959	91046	12.02.2013	0,500
00005959	91041	11.02.2013	6,500

So what I would like to accomplish is to have an extra column in the time sheet table where I multiply [Number] with [Price_2013].

[alexandros17]

The easiest thing to do is to left join the second table with the first and then compute the values for the new column. If you need an example let me know

[#3]

I would very much like a code example

[#4 · Accepted solution]

You can first use left join then caculate the column.

like

tab1temp:

LOAD ActTyp,

     Price_2013

FROM

D:\Projects\ClikView\TEst\tab1.txt

(txt, codepage is 1256, embedded labels, delimiter is '\t', msq);

left join

LOAD Pers.No.,

     ActTyp,

     Date,

     Number

FROM

D:\Projects\ClikView\TEst\tab2.txt

(txt, codepage is 1256, embedded labels, delimiter is spaces, msq);

NoConcatenate

tab1:

load ActTyp,

     Price_2013,

     Pers.No.,  

     Date,

     Number * Price_2013 as Price

     Resident tab1temp;

     drop table tab1temp;

Hopefully it will work.

[alexandros17]

SET

ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';



Total:

LOAD * Inline

[

ActTyp,Price_2013

91011,1007

91012,1007

91013,1007

91014,1007

91015,1007

91016,1007

91021,936

91022,936

91023,936

91024,936

91025,936

91026,936

];



Left Join



LOAD * Inline

[Pers.No.,ActTyp,Date,Number

00005959,91041,15.02.2013,7,500

00005959,91046,15.02.2013,0,500

00005959,91041,14.02.2013,7,500

00005959,91046,14.02.2013,0,500

00005959,91041,13.02.2013,6,000

00005959,91046,13.02.2013,1,500

00005959,91046,13.02.2013,0,500

00005959,91041,12.02.2013,7,500

00005959,91046,12.02.2013,0,500

00005959,91041,11.02.2013,6,500

];



AAA:

NoConcatenate

LOAD

*,

Price_2013 * Number as moltipl

Resident Total;



DROP Table Total;

[manideep78]

Is that a sample data or else your original data which you have to work with???

In the above data there is a matching column ActTyp. But the data in two table fileds are entirely different.

You should have atleast one matching record in ActTyp Column so that you can get your required ouput.

I have changed some records to get the desired output. I left remaining records as they are.

see attached qvw.

[#7]

This is just a fragment of the actual data yes. There are many more columns and rows. I have snipped it down to the most relevant columns.

[#8]

Is there a more effective way other than using load * inline?

There is a lot of data there, and there must be a better way to do it?

[manideep78]

Where your data is getting from???

You can load you data from csv files, excel files and also from Database.

If you are getting your data from any of the files then no need of using load * Inline you can directly load from that file.