10 Replies Latest reply: Jul 31, 2013 1:05 PM by Pablo Abayian RSS

    Loading historical data into existing table

    Peter Schulz

      Hi,

       

      I have a table where some old data is missing and I can't load it directly from the source. So i exported an excel sheet with the needed data. When I try to join the data into the existing table I get weird results...

       

      Table to import has the following columns:

       

      Order No., Start Date, End Date, process time 1, process time 2

       

      The columns exist(and others) in the table in my QV app and I want to fill the empty fields. The history data can contain orders which I don't have in my actual table. Rows not matching the Order No should be dropped. So that no rows are added to my table. Only the missing values should be filled.

       

      I hope someone can help me with that.

        • Re: Loading historical data into existing table

          Hello

           

          This is an example for this kind of script:

           

           

          Increment:

          LOAD Order No., Start Date, End Date, process time 1, process time 2

          **From your excel Spreadsheet

          Concatenate

          LOAD Order No., Start Date, End Date, process time 1, process time 2

          **From your QV app

          Where NOT Exists (Order No.);

          STORE Increment into [..\QVD\Orders.qvd]

           

           

          Please let me know if you have any questions
          Regards,

           

           

           

          Pablo

            • Re: Loading historical data into existing table
              Peter Schulz

              Hi Pablo,

              thank you for your quick response.

               

              But I think it doesn't work this way. I tried it and the result seems not to be correct. Perhaps it is not clear what I want.

               

              I think concatenate just adds rows to my table, but in most cases the rows a already there in my table in my qv app. I just need to fill up gaps in the data. For example th "order no" it should be already there, there don't have to be added other "order no". Perhaps I expressed me wrong...

               

              I have to look up the order number and "update" the content of the fields "Start Date, End Date, process time 1, process time 2" (because in most cases they are empty). Without adding additional lines to my table.

               

              I tried it with inner join and it worked not bad. But i couldn't prevent that additional lines are added to the table.

                • Re: Loading historical data into existing table
                  Peter Schulz

                  Ok the solution is near

                   

                  TABtoBeUpadted:

                  LOAD * INLINE [

                      Order, SubOrder, Start, Ende, Bezeichnung, desc

                      10001, 100, 01.01.2012, ,test123,blabla

                      10002, 100, 01.01.2012, ,bla

                      10003, 100, , 10.01.2012,,bla

                  ];

                   

                  //Historical Data to fill gaps

                  Right Join LOAD * INLINE [

                      Order, SubOrder, Start, Ende, Bezeichnung

                      10001, 100, 01.01.2012, 10.01.2012,test12345

                      10002, 100, 01.05.2012, 10.08.2012,TEST

                      10003, 100, 01.01.2012, 10.01.2012,WORKS

                      10004, 100, 01.01.2012, 10.01.2012,not included

                      10004, 200, 01.01.2012, 10.01.2012,not needed

                  ] WHERE EXISTS(Order);

                   

                  It overwrites Values in original table thats ok, but the field desc, will be overwritten with empty values. How can I avoid that? Would be ok with values from the historical data, but not to replace values with empty "values".

                    • Re: Loading historical data into existing table

                      I get the example now:
                      You want only the records in the first table to be updated (not add the ones that are on table number 2) but you want to keep the description, that you don't have in table 2.

                      Is that correct?
                      Please let me know and I will try to figure out a solution
                      Regards,

                       

                      Pablo

                        • Re: Loading historical data into existing table

                          This Script will do what I think you want:

                          TABtoBeUpadted:

                          LOAD * INLINE [

                              Order, SubOrder, Start, Ende, Bezeichnung, desc

                              10001, 100, 01.01.2012, ,test123,blabla

                              10002, 100, 01.01.2012, ,bla

                              10003, 100, , 10.01.2012,,bla

                          ];

                          NewTable:

                          LOAD * INLINE [

                              Order, SubOrder, Start, Ende, Bezeichnung

                              10001, 100, 01.01.2012, 10.01.2012,test12345

                              10002, 100, 01.05.2012, 10.08.2012,TEST

                              10003, 100, 01.01.2012, 10.01.2012,WORKS

                              10004, 100, 01.01.2012, 10.01.2012,not included

                              10004, 200, 01.01.2012, 10.01.2012,not needed

                          ] ;

                           

                           

                          Result:

                          LOAD Order, desc

                          Resident TABtoBeUpadted;

                          Inner Join

                          LOAD Order, SubOrder, Start, Ende, Bezeichnung

                          Resident NewTable;

                           

                           

                          Drop Tables TABtoBeUpadted, NewTable;

                  • Re: Loading historical data into existing table
                    Gabriel Oluwaseye

                    Hi,

                     

                    In this case, I will suggest using INNER JOIN. INNER JOIN will match records already in your Excel file from QV app and update any field(s) empty with the record from other files, which in your case QV app.

                     

                     

                    QVTable:

                    select * from your excel;

                    inner join select * from QV app;

                     

                    Please replcae * with fields from you application

                     

                    This will also drop unmatched fields.

                     

                    Let me know if this work

                     

                    Best Regards,

                    Gabriel

                      • Re: Loading historical data into existing table
                        Peter Schulz

                        Hi Gabriel,

                        you are right, I just need to do this once.

                         

                        I made the following:

                         

                        1. Created a new qvw file --> history.qvw

                             Loaded all data(old and actual) in 2 tables,

                             made som corrections to field names, that they fit to actual data,

                             Made the inner join and got a pretty clean table, with all fields not just the ones which should be updated.

                             Storing table in history.qvd

                         

                        2. Main App

                             LOAD history.qvd

                             Concatenate LOAD actual data fields FROM source where not exists(keyfield)

                         

                        Voila So it is a combination of all suggestions...

                         

                        3% percent of the orders are not correct. But I have to look where this comes from.

                         

                        Thank you all for your support

                          • Re: Loading historical data into existing table
                            Peter Schulz

                            Hi,

                             

                            sadly I recognized that there is something wrong with the data.

                             

                            The first step with joining works pretty well und everything seems fine.

                             

                            But when it comes to concatenation it goes wrong:

                             

                            Concatenate(QVAPPDATA)

                            LOAD *

                            RESIDENT UPDATEDDATA;

                             

                            First thing - IF I Concatenate(UPDATEDDATA) ... first the updated data is totally wrong. There are 2 or even 4 rows of the data.

                             

                            First I didn't saw that I forgot to check a second field Orders is not unique, only Order+SubOrder... I tried the following:

                             

                            LOAD ... RESIDENT ... WHERE NOT Exists(Order) and NOT Exists(SubOrder);

                             

                            However I do it, I get no suborders for the data coming from db or If I get the (new) suborders I get old and updated suborders mixed up... devils circle ... can someone help me out?

                             

                            The fields loaded in the tables, which should be concatenated, are exactly the same. If this doesn't work I will make a cut at a specific Order No and I load just new values for Order No > than in the history file.

                             

                            Why does it matter which direction I use for concatenation? Appending rows to a table should always have the same result?

                             

                            Thanks for any help.