Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peschu123
Partner - Creator III
Partner - Creator III

Loading historical data into existing table

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.

1 Solution

Accepted Solutions
Not applicable

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;

View solution in original post

10 Replies
Not applicable

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

peschu123
Partner - Creator III
Partner - Creator III
Author

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.

peschu123
Partner - Creator III
Partner - Creator III
Author

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".

Not applicable

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

Gabriel
Partner - Specialist III
Partner - Specialist III

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

Not applicable

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;

peschu123
Partner - Creator III
Partner - Creator III
Author

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

peschu123
Partner - Creator III
Partner - Creator III
Author

Hey Pablo,

didn't saw your reply at my last post.

Thats nearly exactly what I've done now.

Thank you very much.

peschu123
Partner - Creator III
Partner - Creator III
Author

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.