1 Reply Latest reply: May 15, 2014 10:01 AM by Nigel Winton RSS

    Combining Multiple Fields

    gico1972 _

      [U]Problem Solved[/U][/B]

       

       

       

       

      I am a little stuck that in that i need to have multiple date columns in my table combined so that data is shown in my summary as one field.

       

      Data represents sales on a specific flight whereby there is a sold value on the outbound (OB) flight and Inbound Flight (IB).

       

      I simply need to have a summary converting the original Flight (OB) and Flight  (IB) combined to a new Field Called "Flight Number"

       

      The example below shows the original data and underneath how i want my summary shown.

       

      Any help on this would be most welcome.

       

      Regards:)

       

       

       

      [U]

      Original Table     /U[/B]                    

       

      Flight Code     Date     Flight (OB)     Sold (OB)     Flight (IB)     Sold (IB)

      SA11     02-Nov-03     PP222     22     PP223     33

      SA11     09-Nov-03     PP222     19     PP223     26

      SA11     16-Nov-03     PP222     18     PP223     28

      SA11     23-Nov-03     PP222     12     PP223     44

      SA11     30-Nov-03     PP222     22     PP223     32

      SA11     07-Dec-03     PP222     19     PP223     22

      SA11     14-Dec-03     PP222     34     PP223     43

      SA11     21-Dec-03     PP222     43     PP223     31

      SA11     28-Dec-03     PP222     29     PP223     34

      SA11     04-Jan-04     PP222     43     PP223     32

      SA11     11-Jan-04     PP222     28     PP223     18

      SA11     18-Jan-04     PP222     53     PP223     16

      SA11     25-Jan-04     PP222     36     PP223     22

      SA11     01-Feb-04     PP222     22     PP223     24

      SA11     08-Feb-04     PP222     16     PP223     26

      SA11     15-Feb-04     PP222     19     PP223     43

      SA11     22-Feb-04     PP222     31     PP223     32

      SA11     29-Feb-04     PP222     22     PP223     20

                                

      [U]Revised Summary[/U][/B]                         

       

       

      Flight Code     Date     Flight     Sold          

      SA11     02-Nov-03     PP222     22          

      SA11     09-Nov-03     PP222     19          

      SA11     16-Nov-03     PP222     18          

      SA11     23-Nov-03     PP222     12          

      SA11     30-Nov-03     PP222     22          

      SA11     07-Dec-03     PP222     19          

      SA11     14-Dec-03     PP222     34

      SA11     21-Dec-03     PP222     43

      SA11     28-Dec-03     PP222     29

      SA11     04-Jan-04     PP222     43

      SA11     11-Jan-04     PP222     28

      SA11     18-Jan-04     PP222     53

      SA11     25-Jan-04     PP222     36

      SA11     01-Feb-04     PP222     22

      SA11     08-Feb-04     PP222     16

      SA11     15-Feb-04     PP222     19

      SA11     22-Feb-04     PP222     31

      SA11     29-Feb-04     PP222     22

      SA11     02-Nov-03     PP223     33

      SA11     09-Nov-03     PP223     26

      SA11     16-Nov-03     PP223     28

      SA11     23-Nov-03     PP223     44

      SA11     30-Nov-03     PP223     32

      SA11     07-Dec-03     PP223     22

      SA11     14-Dec-03     PP223     43

      SA11     21-Dec-03     PP223     31

      SA11     28-Dec-03     PP223     34

      SA11     04-Jan-04     PP223     32

      SA11     11-Jan-04     PP223     18

      SA11     18-Jan-04     PP223     16

      SA11     25-Jan-04     PP223     22

      SA11     01-Feb-04     PP223     24

      SA11     08-Feb-04     PP223     26

      SA11     15-Feb-04     PP223     43

      SA11     22-Feb-04     PP223     32

      SA11     29-Feb-04     PP223     20

        • Combining Multiple Fields
          Nigel Winton

          Hi there

          This is possible with 2 summaries and a project export.

          First set up your model to capture the columns and name them:-

          Flight Code; Date; OB FN; OB Sold; IB FN; IB Sold.

           

          Now you need 2 summaries

          First one will be for Outbound Flights and Consist of Key Fields :-

          Flight Code Date OB FN and OS sold as the measure

          Second will be for Inbound Flights with Flight Code and Date and IB FN with IB Sold as the measure.

          Now comes the tweaky bit. In both Summaries change the field name of the ** FN and **Sold fields to Flight Number And Sold respectively. So you will now have 2 summaries with exactly the same field names.

          Now got to Project Exports and set up an export to send the first summary to your selected file. Then duplicate this export to send the second summary to the same file, but appending it to the Excel page you exported the first summary to.

          This will give you the layout you require.

          If you want to see this in action, I have a positng on DataKruncher's website ********************.

          Or I can send you the model and project file I have set up if you PM me with your email address.

           

          Good Luck

           

           

          Nigel