topic Date in qvd in QlikView

Date in qvd

farolito20 — Tue, 18 Dec 2012 14:15:08 GMT

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 — Tue, 18 Dec 2012 14:32:08 GMT

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

farolito20 — Tue, 18 Dec 2012 14:35:58 GMT

My data will look like this:

ID, Name, date_modifed

001,Marina, 18/12/2012

Re: Date in qvd

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 14:41:12 GMT

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

farolito20 — Tue, 18 Dec 2012 14:44:40 GMT

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

Re: Date in qvd

farolito20 — Tue, 18 Dec 2012 14:46:55 GMT

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 — Tue, 18 Dec 2012 14:51:10 GMT

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

farolito20 — Tue, 18 Dec 2012 14:51:42 GMT

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

Re: Date in qvd

farolito20 — Tue, 18 Dec 2012 14:56:39 GMT

I jus wanna change de date of the new values added

Re: Date in qvd

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 15:00:35 GMT

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

farolito20 — Tue, 18 Dec 2012 15:03:49 GMT

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

Re: Date in qvd

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 15:08:58 GMT

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

farolito20 — Tue, 18 Dec 2012 15:25:12 GMT

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 — Tue, 18 Dec 2012 15:41:19 GMT

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

farolito20 — Tue, 18 Dec 2012 15:44:06 GMT

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

Re: Date in qvd

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 15:51:02 GMT

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

farolito20 — Tue, 18 Dec 2012 15:52:43 GMT

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 — Tue, 18 Dec 2012 16:09:58 GMT

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

farolito20 — Tue, 18 Dec 2012 22:56:52 GMT

What is Hash128?

Re: Date in qvd

Miguel_Angel_Baeyens — Wed, 19 Dec 2012 07:19:41 GMT

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