topic Re: Single Excel, Date stamp based on Reload in QlikView

Single Excel, Date stamp based on Reload

vardhancse — Mon, 17 Jul 2017 06:46:20 GMT

Hi,

My requirement is as below:

1. Single Excel sheet with some columns and rows

2. In initial reload will hard code the date as (Date(Left('2017-03-28',10),'YYYYMMDD') as Date_ID)

3. Load all the data and will stamp the hard code date(20170310)

4. Post that based on reload time date stamp need to be stamped and load the total records in excel

5. Excel will not have any historic data

Can any one please let me know how to implement below scenario:

1. QVD should have historic data stored

2. Daily refresh will be triggered, only new records need to be loaded and appended to existing QVD

3. If no new records then reload should not happen

**Using my script, for every refresh the no. of records are getting increased(ideally it should not happen)

PFA sample excel and QVW for reference

Re: Single Excel, Date stamp based on Reload

vardhancse — Mon, 17 Jul 2017 09:16:14 GMT

tresesco‌ gwassenaar‌

can please help me out.

Re: Single Excel, Date stamp based on Reload

tresB — Mon, 17 Jul 2017 09:27:05 GMT

You said that excel would not have any historic data. Perhaps that's why there is no check if the data in excel is fresh or not, hence it gets loaded every time you run a reload. How do you want this to behave like? Do you want to keep a check if the data in excel is old or not? If so then put a check.

Re: Single Excel, Date stamp based on Reload

vardhancse — Mon, 17 Jul 2017 09:42:34 GMT

Hi Tresesco,

Yes, in excel historic data will be saved.

when ever we reload, all the records in excel should be loaded and date based on reload should be stamped

however, its a daily refresh. lets assume today we reloaded the QVW and the records@10 are loaded and today date is appended.

If we load once again today, then check should be done to see existing records and then append new records prior to previous reload.

If we are reloading daily once, then check is not required.

Re: Single Excel, Date stamp based on Reload

tresB — Mon, 17 Jul 2017 10:10:01 GMT

In that case, create a date field instead of string and use that as a key field. You have to read the qvd every time you try to append the data (assuming you have only case of appending and no deletion or modification in data). Then use exists() to check is the historical data (in qvd) has already loaded with the same date key. For detailed procedure you may refer: Incremental loading - All scenarios

Re: Single Excel, Date stamp based on Reload

vardhancse — Mon, 17 Jul 2017 10:32:00 GMT

thank you.

Once can please see to my attached app and can do some script changes please.

Re: Single Excel, Date stamp based on Reload

vardhancse — Tue, 18 Jul 2017 03:11:36 GMT

Hi Tresesco,

Based on your suggestion, followed the URL and tried similar script.

Working fine as expected in my local system, but when I run task in QMC the task is getting failed.

Error: Field not found when doing concatenation.

Could you please let me the possible method to solve the same.

Re: Single Excel, Date stamp based on Reload

tresB — Tue, 18 Jul 2017 04:55:23 GMT

Could you post your modified script?

Re: Single Excel, Date stamp based on Reload

vardhancse — Wed, 19 Jul 2017 05:33:48 GMT

Hi Tresseco

Issue is resolved, error is due to path.

Re: Single Excel, Date stamp based on Reload

vardhancse — Tue, 01 Aug 2017 05:27:58 GMT

Hi Tresco,

Here is the process followed:

1. No date column in source, so based on reload time stamping the date into new date column

2. No primary key, so concatenated one field from source with date(new column)

And implemented the logic based on the provided URL.

Now issue is that when ever I reload same records are being loaded with today() date, for e.g:

in July 1st, new records loaded.

In July 2nd, If I reload the same records are loaded and new date is being stamped.

So over all for the month of July, will have multiple of no of records which is wrong.

could you please let me know the check condition I need to verify before loading