topic Re: Adding a calculated column to the data in QlikView

Adding a calculated column to the data

Wed, 20 Feb 2013 08:05:41 GMT

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

Re: Adding a calculated column to the data

alexandros17 — Wed, 20 Feb 2013 08:08:48 GMT

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

Re: Adding a calculated column to the data

Wed, 20 Feb 2013 08:14:27 GMT

I would very much like a code example

Re: Adding a calculated column to the data

Wed, 20 Feb 2013 08:39:49 GMT

You can first use left join then caculate the column.

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.

Re: Adding a calculated column to the data

alexandros17 — Wed, 20 Feb 2013 09:01:49 GMT

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;

Re: Adding a calculated column to the data

manideep78 — Wed, 20 Feb 2013 09:02:19 GMT

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.

Re: Adding a calculated column to the data

Wed, 20 Feb 2013 09:05:36 GMT

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.

Re: Adding a calculated column to the data

Wed, 20 Feb 2013 09:07:31 GMT

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?

Re: Adding a calculated column to the data

manideep78 — Wed, 20 Feb 2013 09:13:45 GMT

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.