topic Loading historical data into existing table in QlikView

Loading historical data into existing table

peschu123 — Mon, 29 Jul 2013 16:32:08 GMT

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.

Re: Loading historical data into existing table

Mon, 29 Jul 2013 16:58:30 GMT

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

Re: Loading historical data into existing table

peschu123 — Tue, 30 Jul 2013 08:10:43 GMT

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.

Re: Loading historical data into existing table

peschu123 — Tue, 30 Jul 2013 09:15:48 GMT

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

Re: Loading historical data into existing table

Tue, 30 Jul 2013 13:12:30 GMT

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

Re: Loading historical data into existing table

Gabriel — Tue, 30 Jul 2013 13:21:10 GMT

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

Re: Loading historical data into existing table

Tue, 30 Jul 2013 13:43:33 GMT

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;

Re: Loading historical data into existing table

peschu123 — Tue, 30 Jul 2013 15:14:37 GMT

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

Re: Loading historical data into existing table

peschu123 — Tue, 30 Jul 2013 15:16:34 GMT

Hey Pablo,

didn't saw your reply at my last post.

Thats nearly exactly what I've done now.

Thank you very much.

Re: Loading historical data into existing table

peschu123 — Wed, 31 Jul 2013 16:40:17 GMT

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.

Re: Loading historical data into existing table

Wed, 31 Jul 2013 17:05:41 GMT

Hi!

I saw that when I saw the inline table.

What I would do, is to create a key field, concatenating the Order No and the Sub order no as 1 field (Order&SubOrder as KeyOrder), and then do the Where Not Exists (KeyOrder)

Hope this helps