Date in qvd

[farolito20]

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?

[farolito20]

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

[Miguel_Angel_Baeyens]

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

[farolito20]

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?

[Miguel_Angel_Baeyens]

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

[farolito20]

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

[Miguel_Angel_Baeyens]

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

[farolito20]

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

Do you have any example of that?

[Miguel_Angel_Baeyens]

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

[farolito20]

What is Hash128?

[Miguel_Angel_Baeyens]

It's a function in QlikView that returns a 128 bits alphanumeric hash of the expressions given as parameters.

Check the Reference Manual for further information and more hashing functions.

Hope that helps.

Miguel