Solved

how to add a new field to a qlikview qvd

Posted on 2014-08-01
16
3,705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

How to increase the row limit in Jasper Server.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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