topic Use data from different dimension? in QlikView

Use data from different dimension?

danielact — Thu, 21 Nov 2013 19:05:32 GMT

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?

Re: Use data from different dimension?

Anonymous — Thu, 21 Nov 2013 19:50:30 GMT

Daniel

How does the attached qvw look to you ?

Best Regards, Bill

Re: Use data from different dimension?

danielact — Thu, 21 Nov 2013 19:56:43 GMT

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

Re: Use data from different dimension?

Anonymous — Thu, 21 Nov 2013 20:09:28 GMT

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

Re: Use data from different dimension?

danielact — Thu, 21 Nov 2013 20:12:45 GMT

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

Re: Use data from different dimension?

Anonymous — Thu, 21 Nov 2013 20:58:13 GMT

Daniel

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

Best Regards, Bill

Re: Use data from different dimension?

danielact — Fri, 22 Nov 2013 12:55:07 GMT

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!

Re: Re: Use data from different dimension?

Anonymous — Fri, 22 Nov 2013 14:07:44 GMT

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

Re: Use data from different dimension?

danielact — Fri, 22 Nov 2013 14:11:29 GMT

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.

Re: Use data from different dimension?

Anonymous — Fri, 22 Nov 2013 15:04:29 GMT

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