Use data from different dimension?

[danielact]

I have a dataset of various contracts. The contracts can be renewed, and I want to compare the new data against the old data. The data looks something like this:

Contract	PriorContract	Location	Address
A1	.	1	123 45th St
A1	.	2	125 45th St
A1	.	3	127 45th St
A1	.	4	129 45th St
A2	A1	1	123 45th St
A2	A1	2	125 45th St
A2	A1	3	127 45th St
A2	A1	4	129 45th St

I want to have a table which will show the following:

Contract	Location	Address	PriorAddress
A1	1	123 45th St	123 45th St
A1	2	125 45th St	125 45th St
A1	3	127 45th St	127 45th St
A1	4	129 45th St	129 45th St

I tried using a set analysis to get the PriorAddress field - I did this: =only({1<Contract=P(PriorContract)>} Address)

That gave me this:

Contract	Location	Address	PriorAddress
A1	1	123 45th St	.
A1	2	125 45th St	.
A1	3	127 45th St	.
A1	4	129 45th St	.
A2	1	.	123 45th St
A2	2	.	125 45th St
A2	3	.	127 45th St
A2	4	.	129 45th St

Any idea how I can get to what I need?

[Anonymous]

Daniel

How does the attached qvw look to you ?

Best Regards,     Bill

[danielact]

It looks like you did an Inline load which is different than the way my data is structured.

[Anonymous]

Daniel

Yup, I certainly did an inline load using the data you provided, except adding the 2's to differentiate the addresses.

To save me guessing, it would be useful if you could share something with an inline load that demonstrates the way your data is structured ?

Best Regards,     Bill

[danielact]

It's the first table in my previous post:

Contract	PriorContract	Location	Address
A1	.	1	123 45th St
A1	.	2	125 45th St
A1	.	3	127 45th St
A1	.	4	129 45th St
A2	A1	1	123 45th St
A2	A1	2	125 45th St
A2	A1	3	127 45th St
A2	A1	4	129 45th St

Although I see I did make a mistake in the table I want to get to - I wrote A1 instead of A2. My bad!

I want to have a chart showing this:

Contract	Location	Address	PriorAddress
A2	1	123 45th St	123 45th St
A2	2	125 45th St	125 45th St
A2	3	127 45th St	127 45th St
A2	4	129 45th St	129 45th St

[Anonymous]

Daniel

Revised qvw attached, which hopefully is as per your corrected desired result.

Best Regards,     Bill

[danielact]

No, the initial data load is wrong again. It looks like this:

Contract	PriorContract	Location	Address
A1	.	1	123 45th St
A1	.	2	125 45th St
A1	.	3	127 45th St
A1	.	4	129 45th St
A2	A1	1	123 45th St
A2	A1	2	125 45th St
A2	A1	3	127 45th St
A2	A1	4	129 45th St

There's no Prior Address field in the initial data load! If there was I wouldn't need to be posting here!

[Anonymous]

Daniel

This is the Inline Load I am using.

TableA:

LOAD * INLINE [

    Contract, PriorContract, Location, Address

    A1,, 1, 123 45th St

    A1,, 2, 125 45th St

    A1,, 3, 127 45th St

    A1,, 4, 129 45th St

    A2, A1, 1, 123 45th St 2

    A2, A1, 2, 125 45th St 2

    A2, A1, 3, 127 45th St 2

    A2, A1, 4, 129 45th St 2

];

Could you correct it as per your needs,

Best Regards,    Bill

[danielact]

Sorry, I had just looked at the table viewer, not the script.

Unfortunately, it's not so simple as to just do a join against itself. The original data source has multiple tables that are linked together, and joining would mean having duplicates of lots of tables. I'm looking to do it on the front end, in the chart object if possible, not in the script.

[Anonymous]

Daniel

No worries.

As this thread has got somewhat confused I'd suggest you close it [or delete it] and start a new one.

Best Regards,     Bill