Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Adding a calculated column to the data

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:

ActTypPrice_2013
910111007
910121007
910131007
910141007
910151007
910161007
91021936
91022936
91023936
91024936
91025936
91026936

The second is the time sheet:

Pers.No.ActTypDateNumber
000059599104115.02.20137,500
000059599104615.02.20130,500
000059599104114.02.20137,500
000059599104614.02.20130,500
000059599104113.02.20136,000
000059599104613.02.20131,500
000059599104613.02.20130,500
000059599104112.02.20137,500
000059599104612.02.20130,500
000059599104111.02.20136,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].

1 Solution

Accepted Solutions
Not applicable

Re: Adding a calculated column to the data

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.

8 Replies

Re: Adding a calculated column to the data

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

Not applicable

Re: Adding a calculated column to the data

I would very much like a code example

Not applicable

Re: Adding a calculated column to the data

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.

Re: Adding a calculated column to the data

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
Valued Contributor

Re: Adding a calculated column to the data

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.

Not applicable

Re: Adding a calculated column to the data

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.

Not applicable

Re: Adding a calculated column to the data

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
Valued Contributor

Re: Adding a calculated column to the data

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.