topic plz help in data model in QlikView

plz help in data model

Mon, 26 Jul 2010 16:53:57 GMT

Hi,

I have two tables

Table 1

-------------------

Acct No

Date

xxx

Table 2

----------------

Acct No

Date

Revenue

Buy/Sell

xxx

When I join using Acct no revenue data is not showing properly, the problem is with date... how to I join these two tables so that I can use a list box for date, & other columns to filter.

Thanks in Advance

plz help in data model

shumailh — Mon, 26 Jul 2010 17:02:36 GMT

May be there wud be some account no repeating in table 1 that's why the revenue number wudn't be matching, you need to try distinct account no for revenue expression.

Shumail

plz help in data model

Mon, 26 Jul 2010 17:09:17 GMT

Is the Date column in Table 1 the same as the Date column in Table 2? if so drop the Date field when importing Table 2

What way are you joing the data, left join, concatenate....

If left Join

Table_1:

LOAD

Acct No, Date;

SQL Select * From tableA;

LEFT JOIN ([Table_1])

LOAD

Acct No, Date, Revenue, Buy/Sell;

SQL Select * From tableB;

OR Try

Table_1_Temp:

LOAD

Acct No, Date;

SQL Select * From tableA;

Table_1:

CONCATENATE LOAD

Acct No, Date, Revenue, Buy/Sell;

SQL Select * From tableB;

NOCONCATENATE LOAD

Acct No, Date, Revenue, Buy/Sell

RESIDENT Table_1_Temp;

DROP TABLE Table_1_Temp;

plz help in data model

Mon, 26 Jul 2010 19:29:28 GMT

both are different dates

plz help in data model

Mon, 26 Jul 2010 20:01:18 GMT

QV is joining the tables based on both of the fields that have identical names, which is not what you want.

If the dates are different, as you mention, alias one, or both of your date fields to stop them from joining (and creating a synthetic key). I would recomend giving them a more meaningful name than 'date'. Describe what each of the date fields are actually for. This will solve the issue of the bad join, and make it easier to report on these dates.

plz help in data model

Mon, 26 Jul 2010 20:38:32 GMT

let me give an example for my requirement

Example Table 1:

	Table 1
date	Acct No	Acct Type	Company Name	Tickets	Prod Type
7/26/2010	1000	Retail	ABC Ltd	20	aaa
7/27/2010	1000	Retail	ABC Ltd	10	bbb
7/26/2010	15000	Market	XYZ LTD	1 5	aaa

Example Table 2:

	Table 2
Date	Acct No	Buy/Sell	Revenue
7/26/2010	1000	Buy	500
7/26/2010	1000	Sell	250
7/27/2010	1000	Buy	200
7/27/2010	15000	Buy	500

plz help in data model

Mon, 26 Jul 2010 20:39:18 GMT

let me give an example

Example Table 1:

	Table 1
date	Acct No	Acct Type	Company Name	Tickets	Prod Type
7/26/2010	1000	Retail	ABC Ltd	20	aaa
7/27/2010	1000	Retail	ABC Ltd	10	bbb
7/26/2010	15000	Market	XYZ LTD	15	aaa

Example Table 2:

	Table 2
Date	Acct No	Buy/Sell	Revenue
7/26/2010	1000	Buy	500
7/26/2010	1000	Sell	250
7/27/2010	1000	Buy	200
7/27/2010	15000	Buy	500

plz help in data model

Tue, 27 Jul 2010 21:41:20 GMT

Any Help?

plz help in data model

Tue, 27 Jul 2010 21:49:45 GMT

As I suggested earlier, if your fields, both labled 'date' represent different dates, then they must be aliased to prevent Qlikview from joining them together.

When Qlikview sees two fields that are called the same thing in two different tables, it assumes that this a join condition. If you actually intend to join the records ONLY on account ID, then these date fields must be given different names in your load script. (date AS [Some Other Name])

plz help in data model

Tue, 27 Jul 2010 22:05:57 GMT

Thanks for your help...but that is not my question, I should be able to filter my data using "Date" filed. How to get the common date field as because i have two date fields if i use table 1 date the revenue date wont changes, & if i use table 2 date then the tickets wont changes