Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vardhancse
Specialist III
Specialist III

Single Excel, Date stamp based on Reload

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

9 Replies
vardhancse
Specialist III
Specialist III
Author

tresescogwassenaar

can please help me out.

tresesco
MVP
MVP

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.

vardhancse
Specialist III
Specialist III
Author

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.

tresesco
MVP
MVP

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

vardhancse
Specialist III
Specialist III
Author

thank you.

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

vardhancse
Specialist III
Specialist III
Author

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.

tresesco
MVP
MVP

Could you post your modified script?

vardhancse
Specialist III
Specialist III
Author

Hi Tresseco

Issue is resolved, error is due to path.

vardhancse
Specialist III
Specialist III
Author

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