Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

qlikview load new records into QVD

Guys

I'm tackling qlikview and thought I had it cracked but im still scratching my head on 1 part and this is how to load new records from a database into a qvd file

To help I will load the qvw file so as to aid this.

But basically I want to load a qvd file then once it has the base data into it all I want then is to upload new records.

Regards
Purchasing.qvw.txt
0
DarrenJackson
Asked:
DarrenJackson
  • 13
  • 12
2 Solutions
 
DarrenJacksonAuthor Commented:
Rob (tagit)  this is targeted at you ;)
0
 
RobOwner (Aidellio)Commented:
*laughing* ok here I am (rubbing eyes, yawning)... sorry had to sleep... I know bad form. I'd like to say it won't happen again but ...

I'll have a look at your qlkiview but for simplicity I"m leaning toward a sample file with sample data to show you how to go about this
0
 
RobOwner (Aidellio)Commented:
Up to you, but I think there's too much room for error here because if you were to reload by accident it would load the base data but wipe out new records so how would you get the new records back?

You can set permissions to only allow a partial reload but what I've found by having the base data in it's own qvd is that I can re-use it with other qvd applications.  Depends on the data and how it's used but thought you should be aware of this first.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DarrenJacksonAuthor Commented:
Wiping base data is totally fine I'm more looking to have the reload quick and update the base data with new records but if it accidentally wipes it it's not  end of world

Lol your let of mate to sleep ;)
0
 
DarrenJacksonAuthor Commented:
I'm looking to have a mechanism I can use for any table that is big to be updated
0
 
RobOwner (Aidellio)Commented:
Can you please identify for me what is your base data and what is your additional data in the load/selects?

Essentially you'd do it something like this:

TABLENAME:
SELECT * FROM [table];
ADD LOAD * FROM [table];
0
 
DarrenJacksonAuthor Commented:
It's the same table I. This excercise the table is PURCHASE_ORDER as you can guess it holds purchase orders which gets updated frequently so say for example I upload the base table at 8 am in the morning by 10am there would say be another 200 records in this table but I don't want to reload all the records I just want it to look at the new records and upload to Qlickview the newly created 200 records
0
 
RobOwner (Aidellio)Commented:
Just out of interest, how long does it take to load all the data. I ask because you would typically only use ADD and REPLACE with massive datasets e.g. million+ records
0
 
DarrenJacksonAuthor Commented:
For this exercise it takes 5 minutes only about 400,000 records but this is just a test other tables are into there millions
0
 
RobOwner (Aidellio)Commented:
Ok sure. I'll be able to look at it more in depth tomorrow. I'll have a think about the best way to approach it
0
 
RobOwner (Aidellio)Commented:
It's going to look something like this:

PURCHASE:
' base data here
 SQL SELECT *
 FROM IFSAPP.PURCHASE_ORDER;
 
 ' Only this table will load with partial reload
 ' concatenate will happen automatically if the number of fields are the same
 ADD LOAD OBJID,
 OBJVERSION,
 ORDER_NO,
 COMPANY,
 VENDOR_NO
 from [U:\Qlikview\PURCHASE.qvd] (qvd)
 where not Exists (objid);
0
 
DarrenJacksonAuthor Commented:
Thanks Rob ill take a look

Cheers
0
 
DarrenJacksonAuthor Commented:
Haven't abandoned you Rob just been side tracked with other work
0
 
RobOwner (Aidellio)Commented:
All good - I'm still working away on my VM that has Qlikview on it, was giving me issues so I should be in a better place to test should the above not do what you want.
0
 
DarrenJacksonAuthor Commented:
Rob

Got this working but its not storing the incremental back into the qvd file.

Any ideas as to why also I'm on a weeks holiday as from tonight so if you cant reply back by then Ill take it up with you in a weeks time.

Regards
0
 
RobOwner (Aidellio)Commented:
No problem - I'll have a think and catch up with you then, just post here when you're back.
0
 
DarrenJacksonAuthor Commented:
Ok cheers Rob
0
 
DarrenJacksonAuthor Commented:
I'm back just had to catchup on work so sorry for the delay. Have you had any thoughts on how to refresh the data then get it to append the qvd file so it is only appending the changes.

Regards
0
 
RobOwner (Aidellio)Commented:
Hi Darren,

Sorry I haven't replied sooner.  I've only just got my VM working with Qlikview so I'll have a look into it right now.

Rob
0
 
RobOwner (Aidellio)Commented:
http:#a40248692
Got this working but its not storing the incremental back into the qvd file.
Can you explain what you mean by this as you're loading from the qvd aren't you?
0
 
DarrenJacksonAuthor Commented:
Hi Rob,

What I basically saying / wishing to happen is this

I create a qvd based on a select statement create my document then when I go to refresh the data instead of it taking several hours to update with ALL NEW data all I would like is that the changes  or NEW ROWS be added to the same QVD file that is being used in the report,

If I'm mistaken on anything then I apologise I am very new at this and still finding my feet.

Thanks
0
 
RobOwner (Aidellio)Commented:
No need to apologise... I've used Qlikview for years but that doesn't mean it's the "right" way.  I've used it for the purposes that the business needed at the time.

*light bulb moment* - ok I'm not sure you can incrementally update a qvd.  I'm under the impression you can only write it once and if you want to update it, then you need to overwrite it each time.  I could be wrong but this is what I ended up having to do.

STORE works like an export as you can use it to create text files and I've confirmed that the old data is overwritten with this command.

I had yearly sales that I had to try and manage like this. I just ended up creating the qvd yearly, where I had each years sales as text files and the qvw that generated the qvd, looped over these text files to create the archive qvd.  This is where the business could operate on 3-4 years of sales data, anything more than that starts to become irrelevant in the current market.  I'm not sure what it's like in your industry?  We did occassionally have to go back to years before that but the rarity and obscurity of that would justify the time for me to do it manually.
0
 
RobOwner (Aidellio)Commented:
Our "incremental" was Year to Date (YTD) sales, which in our case was easier to identify that a "version" in your data.

But I know it's going to be much faster to query the QVD than the SQL DB you've got so I would look at exporting Purchase orders on a yearly basis to their own QVD files.  You can then bring them together as needed into an archive of say the last 3 years if that suits (just drop the oldest year, add the newest).  Using that archive in conjunction with the SQL DB, you can "peek" for the highest version in the QVD and then query the SQL for the incremental.
0
 
DarrenJacksonAuthor Commented:
mmm I having an official training session next week and from this I might understand how this gem of a piece if software works.

For now I will end this and if I end up with more questions which I am sure will happen I will no doubt darken your door again.

Many Thanks rob for your help so far

Regards Darren
0
 
RobOwner (Aidellio)Commented:
Yeah I'm sorry there's no elegant solution to this.  If you find out anything amazing at your training or you've posted a question you want me to look at, feel free to let me know via a message (click on my profile and hit the Message button).

Cheers,

Rob
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now