Discussion Board for collaboration on QlikView Scripting.
I have the following data structure in my qvd:
I would like to make a script that updates the company, e-mail, and phone number by loading only the newest information for Name 1 and 2, but keeps the information about Name 3 since i do not have any updates information about this person.
The table i would like looks like this:
Any suggestions of an easy way to do this in the script?
Go to Solution.
Your key is field CEO?
If your data in QVD is already ordered by Date asc, just try a where not exist clause:
LOAD * FROM File.qvd (qvd)
WHERE not exists (CEO);
edit: Ah, of course it needs to come in sorted descending by Date. I assume it does not.
But you can load your resident table ordered correctly (take care not to auto-concatenate:
LOAD Date as TmpDate, Company, CEO, [e-mail], [Phone number] FROM File.qvd (qvd);
LOAD TmpDate as Date, Company, CEO, [email], [Phone number] RESIDENT T1
WHERE NOT EXISTS(Date, TmpDate)
ORDER BY Date desc;
drop table T1;
I think it was too late.., you need to alias the CEO instead of the Date (still order by Date).
Thanks for the reply,
I have arranged so that my qvd shows all rows sorted by date descending.
However, the "Where not exists (CEO)" does not remove names with the old dates when i load.
The present order of my qvd with descending dates:
And i want to store this table into a qvd:
Can you post the script you tried?
Load * Inline [ Date,Company,CEO,e-mail,Phone number March 3 2015,Comp D,Name 2,XXX@compD.com,000000000 March 3 2015,Comp B,Name 1,XXX@compB.com,111111111 Jan 2. 2014,Comp C,Name 2,XXX@compC.com,222222222 Jan 2. 2014,Comp A,Name 1,XXX@compA.com,333333333 Jan 2. 2014,Comp E,Name 3,XXX@compE.com,444444444 ] Where Not Exists(CEO);
Knut, exists() checks against all previously (and within the current load) loaded field values.
If you loaded another table with CEO before, this might interfere.
But it should not load in all duplicate lines for CEO when loading your address table.
As Anbu suggested, could you post your script or a small sample QVW?
It seems this is not necessary!
I organized my script to transfrom and store the sorted table in a qvd first, and then load only the data I wanted from this qvd to create the data table i wanted to show in qlik.
Consequently, forgetting to drop the first table interferred when I tried to load only the newest data the second time.
Thanks for your help!!
Hi knuterik.thorsen ,
Try like this:
Concatenate two table
A,B from table;
Table B :
where not exist key;