how to add a new field to a qlikview qvd
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]
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]
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
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]
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]
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
Rob
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.
ASKER
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]
where not Exists (objid);
STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd]
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]
where not Exists (objid);
STORE PURCHASE INTO [U:\Qlikview\PURCHASE.qvd]
I'm looking at it before that:
PURCHASE:
LOAD OBJID,
OBJVERSION,
ORDER_NO,
COMPANY,
VENDOR_NO; <===== missing FROM before semicolon
PURCHASE:
LOAD OBJID,
OBJVERSION,
ORDER_NO,
COMPANY,
VENDOR_NO; <===== missing FROM before semicolon
ASKER
ahh do you think I have a typo error
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
can I ask the above script will it add new records or should it be able to add new records via this script??
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)
Partial reload only works on those statements you've indicated to be available (of which none apply here that I can see)
ASKER
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
Regards
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
Then when you do the partial replied only those load or select statements are executed
That should say partial reload
ASKER
Objversion field is unique how would I use this to prevent duplicates and upload only new records into my qvd file
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
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
ASKER
Thanks Rob I'm sure I will have more questions but for now I'm happy
Appreciate your time
Darren
Appreciate your time
Darren
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