Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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].
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.
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
I would very much like a code example
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.
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;
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.
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.
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?
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.