8 Replies Latest reply on Jul 11, 2018 1:36 PM by Wilson Klassen

    Comparing 3 datasets

    Wilson Klassen

      I have three datasets which I am trying to compare.  There is a shipping history, open orders and a specific product file.  The shipping history and the open order data are unions of a series of CSV files.  The specific product file is an Excel file.  All three can be linked via a part number field.  I know I can link the shipping history and the product file in one workbook an the open order and product file in a second workbook.

       

      Is it possible to link all three in a single workbook?

        • 1. Re: Comparing 3 datasets
          Jim Dehner

          Hi Wilson

          Really like to see some sample data and understand the business questions you are trying to answer -

           

          If the question is can you link them - yes

          joining at the Order ID and Product is linking them at the line item on the order level ---- you will have all the orders and their subsequent shipment data -

          Join product on product number

           

          you can then do this

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Comparing 3 datasets
            Wilson Klassen

            I have the sample data sets to send, unfortunately, it is not clear to me how to attach/send the files.  How can I send them?

            • 3. Re: Comparing 3 datasets
              Jim Dehner

              did you try joining the files as suggested?

               

              to attach a file first

               

              then

               

              Jim

              • 4. Re: Comparing 3 datasets
                Wilson Klassen

                I was not able to join my files as in your example.  It appears you have the orders and shipment joined using Order number.  My shipment information does not have the order number.

                 

                Unsuccessfully, I did both and inner and outer join of the Order and Shipment data using the product number with a left join of the Order data with Product data with the product number.

                 

                Unfortunately, my screen does not have the "Advanced editor and add an attachment:" option.  So, I'm still at a loss for attaching the files.

                • 5. Re: Comparing 3 datasets
                  Jim Dehner

                  Hi

                  Sorry but I am not going to be able to help if I can't see the data

                  you will only see the Advanced Option form the Inbox - if you are just responding to my post you need to go to the inbox and open the post again

                   

                  No for your data - what does your business use to associate a shipment with an order -

                  normal practice would take an order (and assign an order number) -- that order would generate 1 or more shipments that were each assigned a shipment number but the record would also have a reverence to the order - in most cases that is the order number - orcers can have multiple shipments -

                   

                  so how is a shipment triggered and followed in your business?

                  Jim

                  • 6. Re: Comparing 3 datasets
                    Wilson Klassen

                    I stumbled on how to attach the files.

                     

                    We can have reports which would have the order number information with the shipments.  In the cases for this data, the order data is purely the orders which are still open.  Any order which has been completed has been removed off this list.

                     

                    The shipment data is just the product which was shipped.  For this report, the requirement to have the associated order number was not needed.

                     

                    Hopefully, this will help.

                    Thanks

                    • 7. Re: Comparing 3 datasets
                      Jim Dehner

                      Hi

                      I think I finally figured out what you wanted

                      I think you are looking at past filled demand(shipped orders) and future demand (open orders) for select products

                      and you want to see something like this

                       

                       

                      I understand that you are working with data that is coming from your ERP system - probably will update on a regular basis - could be use as part of your MRP or demand planning forecasting -

                       

                      all that said the data is what it is - just my POV not a fan of the mix of past data and future data - and the product file only includes a small  portion of the total SKU's in the open and shipped data sets -

                       

                      that said  here is an approach (there are others)

                      first UNION your open and shipped data files (it appends the files)

                       

                      you do that as follows ( you can also use a wildcard union which would be easier with frequent updates - see Union Your Data

                      a table name is added to the date - it is  orders and shipping - becomes important later

                      then bring in the Product data and JOIN it as shown

                       

                       

                       

                      to create the "total orders" use the pen qty from the open orders table and the ship qty from the ship table

                       

                       

                      now you need a date filed that mixes the past (Inv date) and the future (i used prom date - it could be the other date whichever is more relevant to the analysis)

                       

                       

                      to created the viz

                       

                      So the initial question is can you combine the data sources Yes

                      but the deeper answer is that how you join them depends on the business question you are trying to analyze and the actual data that is in the source

                       

                      Jim

                      If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                      • 8. Re: Comparing 3 datasets
                        Wilson Klassen

                        Jim

                        I appreciate all the help and all you have done.  It seems as though it should work.  For some reason, I am not able to join the past and future order files.  I think I am trying to do too much in a single step.  I will need to keep the past and future orders separate.

                        Thanks,

                        Wilson