8 Replies Latest reply: Feb 20, 2013 4:07 AM by Robert Foss RSS

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

        • Re: Adding a calculated column to the data
          Alessandro Saccone

          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

            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
                Alessandro Saccone

                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;