Solved

how to add a new field to a qlikview qvd

Posted on 2014-08-01
16
3,583 Views
Last Modified: 2014-08-01
Guys I'm a total novice of qlikview and I am just trying to get to grips on how to use this. So I created a simple load script and I am basically just seeing how it all comes together. But I am having an issue with adding fields to an already existing qvd file. I'm using a qvd as I want to see how qlickview works with large tables and how it handles updates.

my script is this

PURCHASE:
LOAD OBJID,
COMPANY,
VENDOR_NO;
SQL SELECT *
FROM IFSAPP.PURCHASE_ORDER;
concatenate
LOAD OBJID,
COMPANY,
VENDOR_NO
from [U:\Qlikview\PURCHASE.qvd] (qvd)
where not Exists (objid);
STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd];

and I want to add 2 extra columns (OBJVERSION,ORDER_NO) to it like so

PURCHASE:
LOAD OBJID,
OBJVERSION,
ORDER_NO,
COMPANY,
VENDOR_NO;
SQL SELECT *
FROM IFSAPP.PURCHASE_ORDER;
concatenate
LOAD OBJID,
OBJVERSION,
ORDER_NO,
COMPANY,
VENDOR_NO
from [U:\Qlikview\PURCHASE.qvd] (qvd)
where not Exists (objid);
STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd];

any help as what I seem to have to do is close down qlikview delete the qvd then re-open and reload but it errors. But then if I do a partial load all seem ok.

I might be just having a moment here so any help please

regards
0
Comment
Question by:DarrenJackson
  • 9
  • 7
16 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 40233896
Those two fields you're trying to add...where are you eating to pull them from? Usually a LOAD statement has a FROM, which you're missing from the first LOAD.
0
 

Author Comment

by:DarrenJackson
ID: 40233901
The FROM is there Rob

PURCHASE:
 LOAD OBJID,
 OBJVERSION,
 ORDER_NO,
 COMPANY,
 VENDOR_NO;
 SQL SELECT *
******* FROM IFSAPP.PURCHASE_ORDER; ********
 concatenate
 LOAD OBJID,
 OBJVERSION,
 ORDER_NO,
 COMPANY,
 VENDOR_NO
 from [U:\Qlikview\PURCHASE.qvd] (qvd)
 where not Exists (objid);
 STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd];
0
 
LVL 43

Expert Comment

by:Rob
ID: 40233907
I'm looking at it before that:

PURCHASE:
 LOAD OBJID,
 OBJVERSION,
 ORDER_NO,
 COMPANY,
 VENDOR_NO; <===== missing FROM before semicolon
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:DarrenJackson
ID: 40233911
ahh do you think I have a typo error
0
 
LVL 43

Expert Comment

by:Rob
ID: 40233925
Could be. Just thought it might be a good place to start. I can see what you're trying to do loading all the data into a single qvd. I haven't lasted from the same qvd back into a qvd but in theory I guess it should work.
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 40233934
yeah i'm not sure why you've got it in there twice.  Shouldn't it read:

PURCHASE:
 SQL SELECT *
 FROM IFSAPP.PURCHASE_ORDER;
 concatenate
 LOAD OBJID,
 OBJVERSION,
 ORDER_NO,
 COMPANY,
 VENDOR_NO
 from [U:\Qlikview\PURCHASE.qvd] (qvd)
 where not Exists (objid);
 STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd];
0
 

Author Comment

by:DarrenJackson
ID: 40234234
Think I'm getting confused my self.

I'm trying to do a few things here 1 is to add a new field and the other is to add new records maybe I'm just muddling up everything.
0
 

Author Comment

by:DarrenJackson
ID: 40234237
can I ask the above script will it add new records or should it be able to add new records via this script??
0
 
LVL 43

Expert Comment

by:Rob
ID: 40234269
Yes the above script will add new records. Everything in the PURCHASE table is replaced when you click reload.
Partial reload only works on those statements you've indicated to be available (of which none apply here that I can see)
0
 

Author Comment

by:DarrenJackson
ID: 40234298
I know this is outside of my post and I can create a new post if necessary but how would I do an incremental based on objversion

Regards
0
 
LVL 43

Expert Comment

by:Rob
ID: 40234333
That's ok. You'd spilt up the load and select statements and precede the LOAD statement with ADD (to add new records possibly creating duplicates) or REPLACE ( replace the records with what you load)

Then when you do the partial replied only those load or select statements are executed
0
 
LVL 43

Expert Comment

by:Rob
ID: 40234335
That should say partial reload
0
 

Author Comment

by:DarrenJackson
ID: 40234354
Objversion field is unique how would I use this to prevent duplicates and upload only new records into my qvd file
0
 
LVL 43

Expert Comment

by:Rob
ID: 40234371
Hard to say without knowing the full extent of your data but I will say that I had a qvd with sales data. I loaded the sales for the previous year into my qvd. In another qvd I merged the archive with the current data.
What I'm getting at is you may need to separate the qvds and bring the data together in another qvd that you are using for reports
0
 

Author Closing Comment

by:DarrenJackson
ID: 40234377
Thanks Rob I'm sure I will have more questions but for now I'm happy

Appreciate your time

Darren
0
 
LVL 43

Expert Comment

by:Rob
ID: 40234391
Not a problem and thanks for the points. I get notified of the qlikview questions as I used it extensively at my last job and like to help where I can as it's such a cool product
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
How to increase the row limit in Jasper Server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question