Solved

how to add a new field to a qlikview qvd

Posted on 2014-08-01
16
3,212 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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
I'm looking at it before that:

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

Author Comment

by:DarrenJackson
Comment Utility
ahh do you think I have a typo error
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
can I ask the above script will it add new records or should it be able to add new records via this script??
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
That should say partial reload
0
 

Author Comment

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

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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
Comment Utility
Thanks Rob I'm sure I will have more questions but for now I'm happy

Appreciate your time

Darren
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now