qlikview load new records into QVD

Rob (tagit)  this is targeted at you ;)

*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

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.

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

I'm looking to have a mechanism I can use for any table that is big to be updated

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

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

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

For this exercise it takes 5 minutes only about 400,000 records but this is just a test other tables are into there millions

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

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

Thanks Rob ill take a look

Cheers

Haven't abandoned you Rob just been side tracked with other work

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.

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

No problem - I'll have a think and catch up with you then, just post here when you're back.

Ok cheers Rob

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

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

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?

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

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

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