Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table where some old data is missing and I can't load it directly from the source. So i exported an excel sheet with the needed data. When I try to join the data into the existing table I get weird results...
Table to import has the following columns:
Order No., Start Date, End Date, process time 1, process time 2
The columns exist(and others) in the table in my QV app and I want to fill the empty fields. The history data can contain orders which I don't have in my actual table. Rows not matching the Order No should be dropped. So that no rows are added to my table. Only the missing values should be filled.
I hope someone can help me with that.
This Script will do what I think you want:
TABtoBeUpadted:
LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung, desc
10001, 100, 01.01.2012, ,test123,blabla
10002, 100, 01.01.2012, ,bla
10003, 100, , 10.01.2012,,bla
];
NewTable:
LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung
10001, 100, 01.01.2012, 10.01.2012,test12345
10002, 100, 01.05.2012, 10.08.2012,TEST
10003, 100, 01.01.2012, 10.01.2012,WORKS
10004, 100, 01.01.2012, 10.01.2012,not included
10004, 200, 01.01.2012, 10.01.2012,not needed
] ;
Result:
LOAD Order, desc
Resident TABtoBeUpadted;
Inner Join
LOAD Order, SubOrder, Start, Ende, Bezeichnung
Resident NewTable;
Drop Tables TABtoBeUpadted, NewTable;
Hello
This is an example for this kind of script:
Increment:
LOAD Order No., Start Date, End Date, process time 1, process time 2
**From your excel Spreadsheet
Concatenate
LOAD Order No., Start Date, End Date, process time 1, process time 2
**From your QV app
Where NOT Exists (Order No.);
STORE Increment into [..\QVD\Orders.qvd]
Please let me know if you have any questions
Regards,
Pablo
Hi Pablo,
thank you for your quick response.
But I think it doesn't work this way. I tried it and the result seems not to be correct. Perhaps it is not clear what I want.
I think concatenate just adds rows to my table, but in most cases the rows a already there in my table in my qv app. I just need to fill up gaps in the data. For example th "order no" it should be already there, there don't have to be added other "order no". Perhaps I expressed me wrong...
I have to look up the order number and "update" the content of the fields "Start Date, End Date, process time 1, process time 2" (because in most cases they are empty). Without adding additional lines to my table.
I tried it with inner join and it worked not bad. But i couldn't prevent that additional lines are added to the table.
Ok the solution is near
TABtoBeUpadted:
LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung, desc
10001, 100, 01.01.2012, ,test123,blabla
10002, 100, 01.01.2012, ,bla
10003, 100, , 10.01.2012,,bla
];
//Historical Data to fill gaps
Right Join LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung
10001, 100, 01.01.2012, 10.01.2012,test12345
10002, 100, 01.05.2012, 10.08.2012,TEST
10003, 100, 01.01.2012, 10.01.2012,WORKS
10004, 100, 01.01.2012, 10.01.2012,not included
10004, 200, 01.01.2012, 10.01.2012,not needed
] WHERE EXISTS(Order);
It overwrites Values in original table thats ok, but the field desc, will be overwritten with empty values. How can I avoid that? Would be ok with values from the historical data, but not to replace values with empty "values".
I get the example now:
You want only the records in the first table to be updated (not add the ones that are on table number 2) but you want to keep the description, that you don't have in table 2.
Is that correct?
Please let me know and I will try to figure out a solution
Regards,
Pablo
Hi,
In this case, I will suggest using INNER JOIN. INNER JOIN will match records already in your Excel file from QV app and update any field(s) empty with the record from other files, which in your case QV app.
QVTable:
select * from your excel;
inner join select * from QV app;
Please replcae * with fields from you application
This will also drop unmatched fields.
Let me know if this work
Best Regards,
Gabriel
This Script will do what I think you want:
TABtoBeUpadted:
LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung, desc
10001, 100, 01.01.2012, ,test123,blabla
10002, 100, 01.01.2012, ,bla
10003, 100, , 10.01.2012,,bla
];
NewTable:
LOAD * INLINE [
Order, SubOrder, Start, Ende, Bezeichnung
10001, 100, 01.01.2012, 10.01.2012,test12345
10002, 100, 01.05.2012, 10.08.2012,TEST
10003, 100, 01.01.2012, 10.01.2012,WORKS
10004, 100, 01.01.2012, 10.01.2012,not included
10004, 200, 01.01.2012, 10.01.2012,not needed
] ;
Result:
LOAD Order, desc
Resident TABtoBeUpadted;
Inner Join
LOAD Order, SubOrder, Start, Ende, Bezeichnung
Resident NewTable;
Drop Tables TABtoBeUpadted, NewTable;
Hi Gabriel,
you are right, I just need to do this once.
I made the following:
1. Created a new qvw file --> history.qvw
Loaded all data(old and actual) in 2 tables,
made som corrections to field names, that they fit to actual data,
Made the inner join and got a pretty clean table, with all fields not just the ones which should be updated.
Storing table in history.qvd
2. Main App
LOAD history.qvd
Concatenate LOAD actual data fields FROM source where not exists(keyfield)
Voila So it is a combination of all suggestions...
3% percent of the orders are not correct. But I have to look where this comes from.
Thank you all for your support
Hey Pablo,
didn't saw your reply at my last post.
Thats nearly exactly what I've done now.
Thank you very much.
Hi,
sadly I recognized that there is something wrong with the data.
The first step with joining works pretty well und everything seems fine.
But when it comes to concatenation it goes wrong:
Concatenate(QVAPPDATA)
LOAD *
RESIDENT UPDATEDDATA;
First thing - IF I Concatenate(UPDATEDDATA) ... first the updated data is totally wrong. There are 2 or even 4 rows of the data.
First I didn't saw that I forgot to check a second field Orders is not unique, only Order+SubOrder... I tried the following:
LOAD ... RESIDENT ... WHERE NOT Exists(Order) and NOT Exists(SubOrder);
However I do it, I get no suborders for the data coming from db or If I get the (new) suborders I get old and updated suborders mixed up... devils circle ... can someone help me out?
The fields loaded in the tables, which should be concatenated, are exactly the same. If this doesn't work I will make a cut at a specific Order No and I load just new values for Order No > than in the history file.
Why does it matter which direction I use for concatenation? Appending rows to a table should always have the same result?
Thanks for any help.