topic Re: Combining data sources. Issue with how tables link in each in QlikView

Combining data sources. Issue with how tables link in each

Mon, 26 Sep 2011 19:52:49 GMT

I am trying to combine 2 databases but I am running into some issues. I have tried Concatenate Loading our old data base in with our new one. I renamed the fields I want to be listed together. My issue arises when I want to go into more detail.

Example:

Main database has Customer Table, and Order table. They are linked using a customer number,

Old database has Customer and order table. They are linked together using custcode(shortname). Customer number only exists in main database.

I am able to combine the 2 customer tables fine using the customer name. I can combine the order tables fine using the order date fields and order amount fields.

The issue is that I now want to link the customers in with their orders, but since each database connects to the orders differently I am getting the wrong results.

I need it to link the old database fields using custcode, and the main database using customer number.

Combining data sources. Issue with how tables link in each

Anonymous — Tue, 27 Sep 2011 09:22:22 GMT

Without knowing your data or your script I am not sure if this is usefull but did you try to create a key to conect both databases.

Something like:

[Customer Table] &'-' &[Order table] as KEYFIELD,

(or any other fields you want to connect)

Hope this is helpfull

Dennis.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 11:15:04 GMT

I am not sure if I understand creating a like like that. I know I can use the & '-' & as field to rename fields so they can link, but my issue is that I now have 2 tables that connect to each other in 2 ways. All orders in the old database connect with custcode and all orders in the newest database connect with custnum. Custnum is not in the old database and custcode is not in the new database.

Unless you think that doing something like this would work? Company and custnum do not exist in the data from old database so it would only pass custcode and in the new would only pass company and custnum?

company& custnum & custcode as link

Edit: Now that I thik about it I am not sure how to create this link, because in the script I have 4 tables. They are not combined until the script is run, therefor I can not make the link I described above?

Re: Combining data sources. Issue with how tables link in each

Anonymous — Tue, 27 Sep 2011 11:40:40 GMT

Is there a way you can share your source with use. Maybe make two Excel files with the same information in it?

Then I can have a look.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 11:56:49 GMT

Attached is a sample excel book. each sheet is a different table.

Used this load script:

Customer:

LOAD Name,

custnum

FROM

...

(ooxml, embedded labels, table is Customer);

Order:

LOAD custnum,

[order],

orderamt

FROM

...

(ooxml, embedded labels, table is Order);

Cutomer2:

Concatenate(Customer)

LOAD Name,

custcode

FROM

...

(ooxml, embedded labels, table is Oldcust);

Order2:

Concatenate(Order)

LOAD custcode,

Ordernum,

orderamt

FROM

...

(ooxml, embedded labels, table is OldOrder);

Combining data sources. Issue with how tables link in each

Anonymous — Tue, 27 Sep 2011 12:32:55 GMT

What you could do first is join the two pair (old with old and main with main)

for example like this:

Main:

LOAD Name,

custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

[order],

orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Old:

LOAD Name as NameOld,

custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Old)

LOAD custcode,

Ordernum,

orderamt as OrderAmtOld

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

No you have two tables one old and one main.

You can connect them on any field you want.

Combining data sources. Issue with how tables link in each

Anonymous — Tue, 27 Sep 2011 12:35:17 GMT

This will connect them on the name field:

Main:

LOAD Name,

custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

[order],

orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Old:

LOAD Name,

custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Old)

LOAD custcode,

Ordernum,

orderamt as OrderAmtOld

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

This makes the most sense to me, but maybe you want it on an other field ...

Re: Combining data sources. Issue with how tables link in each

SunilChauhan — Tue, 27 Sep 2011 12:37:45 GMT

see the attached file.

hope this help

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 12:42:36 GMT

OK so say I want to show the Monthly order amount for both databases? I was achieving this by linking the order dates from each order table together.

Your example works when I need to see the total ordered per customer without the date information.

So at the moment I am stuck either connecting the customers together, or stuck with linking just the orders together.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 12:49:39 GMT

I guess I did not explain enough. My bad.

I was trying to combine customers and combine orders because I need to look at the data in different ways.

IE:

Total order amount per customer (Works when I link the customers together and then separately link the orders to each customer)

total orderamt per month (Only works when I combine orderamt and orderdates from the order table)

My issue is I need both to work, so I need all order dates from both tables in one field to use as a dimension, and then the sum of orderamt as a expression.

Then I also need all customers in one field for a dimension and the sum of orderamt to use as the expression

Re: Combining data sources. Issue with how tables link in each

Anonymous — Tue, 27 Sep 2011 13:18:01 GMT

Try if this works for you:

Main:

LOAD Name,

custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

[order] as Ordernum,

orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Join (Main)

LOAD Name,

custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Main)

LOAD custcode,

Ordernum,

orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 13:52:36 GMT

This is close, but it still has issues linking the customer names. To see what I mean, create a table and add all fields. You see in the table below that orders 1-7 no longer have customer names. If I create a pivot table with customer name I receive the second chart

custcode	custnum	Name	orderamt	Ordernum
abc			85	1
adg			67	2
fry			588	3
thu			54	4
edf			135	5
abc			21	6
adg			29	7
abc	1	abcdt	55	55
adg	2	adgdt	22	56
fry	3	frydt	23	57
thu	4	thudt	59	58
abc	1	abcdt	68	59
abc	1	abcdt	128	60
edf	5	edfdt	548	61
adg	2	adgdt	2	62
fry	3	frydt	5644	63
thu	4	thudt	66	64

Name	sum(orderamt)
abcdt	251
adgdt	24
edfdt	548
frydt	5667
thudt	125
-	979

Edit:

I am not familiar with it, but could this be done with mapping?

It seems like the issue is that I need all customers to have both custcode and custnum in the script for this to work correctly. Currently I only get this information together after the script load using concatenate.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 14:21:32 GMT

Is this what you want?

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 14:25:27 GMT

Can you explain what you did? I have never used temp tables so I need to wrap my mind around how it works.

This also seems to ignore the new/main database order table.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 14:47:59 GMT

sorry this is what you want to see at the end I guess!

Please see the code.

Ask if you have any doubts. This is all Trial and Error what I did..

Cust:
LOAD Name,
     custcode
FROM
(ooxml, embedded labels, table is Oldcust);
inner Join (Cust) // Inner Join is for loading customers which are present in both Old as well as Main Customer Table but even normal Join also works
LOAD Name,
     custnum
FROM
(ooxml, embedded labels, table is Customer);
OrderOLd:
Noconcatenate
LOAD custcode,
     Ordernum,
     orderamt
FROM
(ooxml, embedded labels, table is OldOrder);
Order:
Noconcatenate
LOAD custnum,
     [order],
     orderamt as Orderamt1
FROM
(ooxml, embedded labels, table is Order);

Regards

Sravan

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 14:59:41 GMT

Your example does correctly link the customers, but in with our actual data there are customers that exist in the old data that do not exist in the new, and customers that exist in the new that do not exist in the old. So the inner join will not work since that returns only customers that exist in both.

Also has the order tables separate, where in our actual data I was combining the orderamt fields and the orderdate fields so I could create a chart that shows orderamount by month.

I am going to try a new approach. We have Cystal Reports, so I was able to take the Old database customers and then add the new database as a subreport. I then assigned the custnum to the customers from the old database and exported it to excel.

So now I have an excel file that lists the Old database Customers that now also has a custnum from the new database in the same file.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 15:14:48 GMT

Ok I seem to be loosing track of what I am trying to accomplish.

I have 4 tables,

Customer 2010-present

OldCustomer 2004-2009

Orders 2010-present

OldOrders 2004-2009

I want to combine all data from customers into 1 table. Customers need to be in one field.

I want to combine all data from Orders. Orderdate, orderamt, and order numbers need to be in their respective fields.

Needs to be full outer joined due to data exisiting in both places.

I need to link Combined Customer table with Combined Order Table.

Customer connects to Orders using Company, and custnum.

OldCustomer connects to OldOrders using custcode.

Results:

Would like to see Order amout per month that pulls data from combined order table.

2004 - 2011 by month

Would like to see Customer orders by year/ month in a pivot using combined customer table and combined order table.

Customer Name, Year Ordered, Month Ordered, Order Amount

ABC,2004,3,15 (From Old Orders)

ABC,2006,2,25 (From Old Orders)

ABC,2007,1,55 (From Old Orders)

ABC,2007,2,86 (From Old Orders)

ABC,2007,3,455 (From Old Orders)

ABC,2008,11,55 (From Old Orders)

ABC,2010,9,64 (From Orders)

ABC,2011,7,51 (From Orders)

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 15:24:47 GMT

You can only concatenate to make one CustomerTable from the following Tables as they may have different customers also.

Customer 2010-present

OldCustomer 2004-2009

with out empty fields it is not possible just for Info.

Re: Combining data sources. Issue with how tables link in each

Tue, 27 Sep 2011 15:29:56 GMT

I was able to combine the tables just fine. I now Have a working list of customers from Both data sources.

I also Have a working chart of orderamt by year.

My only problem is linking the customer tables with the order tables since the databases do the linking differently.

I can't take a customer and see orders from both datasources

Re: Combining data sources. Issue with how tables link in each

john_duffy — Tue, 27 Sep 2011 17:38:17 GMT

Hi Marc.

How about creating a new key field on the two tables. When combining the current and old customer tables, create a new field called Customer_Orders_Key. Records from the current customer table will create this field using Company & Cust_Num. Records from the old customer table will create this field using CustCode.

Do the same when combining the current and old order tables.

Now link the combined customers table to the combined orders table using Customer_Orders_Key.