Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Help me duplicate data on reload

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!

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Help me duplicate data on reload

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

4 Replies
ahaahaaha
Honored Contributor

Re: Help me duplicate data on reload

beck_bakytbek
Valued Contributor III

Re: Help me duplicate data on reload

OmarBenSalem
Esteemed Contributor

Re: Help me duplicate data on reload

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

Not applicable

Re: Help me duplicate data on reload

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);

Community Browser