Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
Hopefully this is easy question to solve - I'm periodically load a bank update file with two tables into QS. The Account Summary: table just holds [account name] and [balance] fields as at the last download date. (The other table is transactional data which I'm fine with).
When I reload the file I simply want to add to rather than replace the [account name] and [balance] data so I can visualise account balance over time.
I also need to create a automated date field in the load script as the download date is not provided in the raw data.
I tried to do some stuff with incremental loads but it all got a bit confusing - surely this cant be that difficult!
Sorry I'm a newbie, can someone pls help me with a simple solution!
Thanks!
load it the first time and store it in a qvd:
account:
load *,today() as date from SourceAccount;
store account into [Path\account.qvd](qvd);
Once you've done this one time. Now change your script as follow:
account:
load * from [Path\account.qvd](qvd);
concatenate
load * , today() as Date)from SourceAccount;
store account into [Path\account.qvd](qvd);
Suppose We've created this qvd today; and then changed the script as mentionned above and executed it tomorrow.
What would happen is:
The script will :
import your QVD file containing today's date and the information of today [account name] and [balance].
account:
load * from [Path\account.qvd](qvd);
Then
it will import the information of tomorrow that exists in your data source and concatenate it to the table and information already existing in your qvd
concatenate
load * , today() as Date)from SourceAccount;
Then, it will create a new qvd of this concatenated table , today and tomorrow's informations and store it in the same location.
store account into [Path\account.qvd](qvd);
With that, everytime we execute the script we'll have all the history of our information in our QVD with a date field we created.
Hope this was clear enough
Hi Ben,
May be use this Loading new and updated records with incremental load ‒ Qlik Sense
Regards,
Andrey
Hi Ben,
look at this :
1.https://www.youtube.com/watch?v=BoEhqyy2xbM
2.QlikView Tutorials | Incremental Loads in Qlikview - YouTube
i hope that helps
beck
load it the first time and store it in a qvd:
account:
load *,today() as date from SourceAccount;
store account into [Path\account.qvd](qvd);
Once you've done this one time. Now change your script as follow:
account:
load * from [Path\account.qvd](qvd);
concatenate
load * , today() as Date)from SourceAccount;
store account into [Path\account.qvd](qvd);
Suppose We've created this qvd today; and then changed the script as mentionned above and executed it tomorrow.
What would happen is:
The script will :
import your QVD file containing today's date and the information of today [account name] and [balance].
account:
load * from [Path\account.qvd](qvd);
Then
it will import the information of tomorrow that exists in your data source and concatenate it to the table and information already existing in your qvd
concatenate
load * , today() as Date)from SourceAccount;
Then, it will create a new qvd of this concatenated table , today and tomorrow's informations and store it in the same location.
store account into [Path\account.qvd](qvd);
With that, everytime we execute the script we'll have all the history of our information in our QVD with a date field we created.
Hope this was clear enough
Thanks heaps Omar. That did the trick!
Here's the final code I came up with (First block commented out after initial load). Much appreciated!
/*[Summary$]:
LOAD [Accounts Summary]as [Account Name],
[F2]as [Account Balance],
Date(today()) as [Balance Date]
FROM [lib://Desktop/report.xls]
(biff, embedded labels, header is 4 lines, table is Summary$);
Store [Summary$] into [lib://Desktop/Summary.qvd'](qvd);*/
[Summary$]:
LOAD *
FROM [lib://Desktop/Summary.qvd'](qvd);
Concatenate
LOAD
[Accounts Summary]as [Account Name],
[F2]as [Account Balance],
Date(today()) as [Balance Date]
FROM [lib://Desktop/report.xls]
(biff, embedded labels, header is 4 lines, table is Summary$);
Store [Summary$] into [lib://Desktop/Summary.qvd'](qvd);