combine and keep data from different sources

[#1]

Dear all,

I have two different systems, from which I am pulling order lines information (item, quantity, order number, customer code, date etc). Being the systems different, the structures of these two tables are also different.

I need to combine these tables to make one single backorder report; this report is an historical log, where each week the new backorder data is appended. But I also need to keep the originals order lines table to make some charts and reports ouf of that.
Further, the backorder table and the two order tables need to be somehow linked to each other.
For example, I would like to select a customer (in the order line table) and see the BO for just that customer.

The only field which is quite consistent within the two tables is the item field.
But, optimally, the BO table should be linked through the line field, as the BO actually happens on the line, not on the part.

If I link through the item field, it happens that: if I select item xyz, I see customers  a, b, c, d are possible selections. Problem is, these are the customers which ever purchased item xyz and perhaps only customer b has a backorder.
This because the link is the part xyz and not the order line.

What is actually done in such situations, when two different sources tables needs to be kept and at the same time combined in one report?
"port" all the order lines fields (many) into the BO table?
normalise date between the two order lines tables, so to recreate a single order lines table?
or...?
in both cases, it seems to me a lot of work.

thank you so much!

[Peter_Cammaert]

You can leave all tables separate (though this may not e the best solution), as long as you create one or more decent keys between their rows. Include a date and an order number if possible. QlikView enjoys grabbing fields from different live tables and putting them together in an apparently single report object.

The trick is in designing a good connection between the different sources. What selection fields do you want to present in your BO report? Do users get period fields or a timeline? Item is a good candidate to be included in these keys. What other selections need to flow through the data model?

Best,

Peter

[datanibbler]

Hi Erik,

well, I'll start from the back here:

=> Once you have a single table with the new information from both sources, appending that to a historization_table is quite easy:

a) load original historization_table

b) load new data

c) append new data to historization_table (you might have to check through a key that you don't append the same records twice and thus create duplicates)

d) store as a qvd

First, however, you need to make one table from the two sources, which is probably the more difficult part:

=> It's hare (impossible) to link or join through a field you don't have, opbviously.

Can you post two small sample files to visualize how your source_data looks and how you would like to combine it?

HTH

Best regards,

DataNibbler

[jonathandienst]

Hi

I would combine the records from both order systems into a single table, but conform the table structures so that customer name, item no, etc all have the same field name (use LOAD .. As .. ).

Are you calculating the BO amounts in the QV document? Either way, I would include the BO amount on the already loaded order lines and would set a BO flag on each order that has gone onto back order. Then you can select the flag (BO = yes) and the item name (xyz), and you will only see the customers with back orders for that item. Clear the BO flag, and you will see all customers who ordered that item.

HTH

Jonathan