21 Replies Latest reply: Dec 21, 2012 4:39 AM by Miguel Angel Baeyens de Arce RSS

    Date in qvd

    Marina Alvarez

      Hello!

       

      I have a database, and I want to make a backup of it, so I'm using a .qvd files.

      Now, I wanna save per data, the hour and date when I add it to my .qvd.

       

      How can I add a new column to my qvd?

        • Re: Date in qvd
          Miguel Angel Baeyens de Arce

          Hi,

           

          Use a variable with the timestamp then add it to the file name:

           

          LET vTimestamp = Timestamp(Now(), 'YYYYMMDDhhmmss');
          
          Data:
          LOAD *
          FROM Table;
          
          STORE Data INTO [File$(vTimestamp).qvd] (qvd);
          

           

          The result will be a QVD named File20121218153100.qvd

           

          Hope that helps.

           

          Miguel

            • Re: Date in qvd
              Marina Alvarez

              My data will look like this:

               

              ID, Name, date_modifed

              001,Marina, 18/12/2012

                • Re: Date in qvd
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  I'd rather speak in English in the general forums, there is a Spanish speaking group where all threads en español are welcome.

                   

                  So, I assume that the field you want to populate is the "date_modified" so a little change to the code above will work

                   

                  LET vTimestamp = Timestamp(Now(), 'DD/MM/YYYY');
                  
                  Data:
                  LOAD *,
                       $(vTimestamp) AS date_modified
                  FROM Table;
                  

                   

                  Hope that helps.

                   

                  Miguel

                    • Re: Date in qvd
                      Marina Alvarez

                      Ok, but this is the load... the $(vTimestamp) AS date_modified should not be in the store?

                        • Re: Date in qvd
                          Marina Alvarez

                          tabla1:

                          LOAD ID,

                               Name,

                               Age

                          FROM

                          prueba.xlsx

                          (ooxml, embedded labels, table is Sheet1);

                           

                           

                          LOAD ID,

                                     Name,

                                     Age

                          FROM bk2.qvd (qvd);

                           

                           

                          NoConcatenate

                          tabla2:

                          LOAD Distinct

                                    ID,

                                     Name,

                                     Age

                          Resident tabla1;

                           

                           

                          STORE tabla2 into bk2.qvd (qvd);

                           

                           

                          That's my code, I wanna add a column with a date when the new data would stored

                            • Re: Date in qvd
                              Miguel Angel Baeyens de Arce

                              Hi,

                               

                              As mentioned above:

                               

                              LOAD ID,
                                   Name,
                                   Age
                              FROM
                              prueba.xlsx
                              (ooxml, embedded labels, table is Sheet1);
                              
                              LOAD ID,
                                   Name,
                                   Age
                              FROM bk2.qvd (qvd);
                              
                              LET vDateModified = Date(Now(), 'DD/MM/YYYY');
                              
                              NoConcatenate
                              tabla2:
                              LOAD Distinct
                                   ID,
                                   Name,
                                   Age,
                                   $(vDateModified) AS date_modified // this field is new, will be stored into bk2.qvd
                              Resident tabla1;
                              
                              STORE tabla2 into bk2.qvd (qvd);
                              
                              

                               

                              Hope that helps.

                               

                              Miguel

                                • Re: Date in qvd
                                  Marina Alvarez

                                  I jus wanna change de date of the new values added

                                    • Re: Date in qvd
                                      Miguel Angel Baeyens de Arce

                                      How do you identify those new values? What is the unique key? Is it possible that a value already exists in both existing and new records?

                                       

                                      Miguel

                                        • Re: Date in qvd
                                          Marina Alvarez

                                          I suposse that the noconcatenate identify the new values, is incorrect that?

                                            • Re: Date in qvd
                                              Miguel Angel Baeyens de Arce

                                              Mmm,

                                               

                                              Nope, NOCONCATENATE specifies that, even when the following LOAD has the same number and name of fields, it must not be concatenated to any previously loaded table. For example, in the script above, you needn't NOCONCATENATE because you are adding a new field that is "date_modified".

                                               

                                              I'd bet on using EXISTS() but for that you need a unique key (or create one if you don't have one) so all records can be distinguished from the others, and you check on the values loaded. Say that in the example, the field ID is unique (cannot store repeated values):

                                               

                                              Source1:
                                              LOAD ID,
                                                   Name,
                                                   Age
                                              FROM
                                              prueba.xlsx
                                              (ooxml, embedded labels, table is Sheet1);
                                              
                                              
                                              Source2: // just for clarification, table will concatenate with previous
                                              LOAD ID,
                                                   Name,
                                                   Age
                                              FROM bk2.qvd (qvd)
                                              WHERE NOT EXISTS(ID); // will only load new values in ID from table Source1
                                              
                                              LET vDateModified = Date(Now(), 'DD/MM/YYYY');
                                              
                                              NoConcatenate
                                              tabla2:
                                              LOAD Distinct
                                                   ID,
                                                   Name,
                                                   Age,
                                                   $(vDateModified) AS date_modified // this field is new, will be stored into bk2.qvd
                                              Resident tabla1;
                                              
                                              STORE tabla2 into bk2.qvd (qvd);
                                              

                                               

                                              Hope that makes sense.

                                               

                                              Miguel

                                                • Re: Date in qvd
                                                  Marina Alvarez

                                                  WHERE NOT EXISTS(ID); don't help me because for example, if I change the price of the product and I wanna keep the other price, because I can use that for reports probably.

                                                   

                                                  For that, I need the both.

                                                   

                                                  How can I add a qvd the new data, just if one of the columns change?

                                                    • Re: Date in qvd
                                                      Miguel Angel Baeyens de Arce

                                                      I figured that out...

                                                       

                                                      As I said, you need a unique key, either in the data source or in QlikView to differentiate each record and a flag field that specifies whether the row has been modified or not, so for instance, if ID 1 has been loaded first with Name "A" and then ID 1 is loaded with Name "B", only the row with name "B" is kept.

                                                       

                                                      So on one hand you do have to load all those ID that do not exist, because they are new, and on the other hand, you need to load those ID that do exist, but have the modification / update flag set.

                                                       

                                                      Hope that helps.

                                                       

                                                      Miguel

                                                        • Re: Date in qvd
                                                          Marina Alvarez

                                                          Yes, but that only work if column ID is modified right? What about the other columns?

                                                            • Re: Date in qvd
                                                              Miguel Angel Baeyens de Arce

                                                              That was only an example.

                                                               

                                                              That would work as long as ID is unique for each "record" but, when the "record" is modified, keeps the same ID. (New data, new IDs, same data, modified or not, same ID).

                                                               

                                                              The trick is to create the update flag that is set when the record is modified (it's likely to be created in the data source). Maybe using hashes for the content to be checked? Check your database manager, it may have a function to check for that.

                                                               

                                                              Hope that helps.

                                                               

                                                              Miguel

                                                                • Re: Date in qvd
                                                                  Marina Alvarez

                                                                  I'm doing this alone, so for that I hope help :/

                                                                  Do you have any example of that?

                                                                    • Re: Date in qvd
                                                                      Miguel Angel Baeyens de Arce

                                                                      Hi,

                                                                       

                                                                      Nothing to be shared so far. But I used some Hash128() functions in QlikView (the RDBM was out of reach to add any stored procedure or similar, triggered when updating records) on the whole row, so if any of the fields changed, the whole hash changed. This hash is stored in one new field.

                                                                       

                                                                      That's why I mentioned doing a load of non existing records (easily identifiable by their ID, no problem with this) and then a second load for existing IDs but different hashes (something changed).

                                                                       

                                                                      Then is all stored into one QVD, and it starts all over again each night when the file is reloaded.

                                                                       

                                                                      // This is not an actual script, syntax should be checked and code should be simplified
                                                                      
                                                                      Data: // Step 1
                                                                      LOAD ID
                                                                      FROM Data.qvd (qvd);
                                                                      
                                                                      NewRows: // Step 2
                                                                      LOAD ID,
                                                                           Name,
                                                                           Date
                                                                           Hash128(ID, Name, Date) AS Check
                                                                      WHERE NOT EXISTS(ID);
                                                                      SQL SELECT ID,
                                                                           Name,
                                                                           Date
                                                                      FROM Table; // This is a database
                                                                      
                                                                      UpdatedRows: // Step 3
                                                                      LOAD ID,
                                                                           Name,
                                                                           Date,
                                                                           Hash128(ID, Name, Date) AS Check
                                                                      WHERE EXISTS(ID) AND NOT EXISTS(Check, Hash128(ID, Name, Date));
                                                                      SQL SELECT ID,
                                                                           Name,
                                                                           Date
                                                                      FROM Table; // Same database again
                                                                      
                                                                      OldRowsNotUpdated: // Step 4
                                                                      LOAD ID,
                                                                           Name,
                                                                           Date,
                                                                           Check
                                                                      FROM Data.qvd (qvd)
                                                                      WHERE NOT EXISTS(ID);
                                                                      
                                                                      STORE NewRows INTO Data.qvd (qvd); // Old Non Updated + New + Old Updated in the same file
                                                                      

                                                                       

                                                                      Hope that gives you an idea.

                                                                       

                                                                      Miguel

                                                  • Re: Date in qvd
                                                    Marina Alvarez

                                                    Every time that I reload my script, all data change the date