Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

combine and keep data from different sources

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!

3 Replies

Re: combine and keep data from different sources

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
Esteemed Contributor

Re: combine and keep data from different sources

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

MVP
MVP

Re: combine and keep data from different sources

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser