topic Re: Evaluating Dimension Fields on both sides in Set Analysis in QlikView

Evaluating Dimension Fields on both sides in Set Analysis

Mon, 17 Mar 2014 10:06:11 GMT

Hi ,

I have concatenated 2 tables as below:

Sales:

LOAD * INLINE [

ID1,AGE

1, 10

2, 20

3, 30

];

Concatenate

Customers:

LOAD * INLINE [

ID2,Name

1, Amy

2, Bob

3, Carla

];

We need to show the following columns in straight table chart:

ID2, Name, ID1, Age

ID1 and Age will only be displayed when value of ID1 is equal to ID2. We have to use only a single table.

Please do let me know the set analysis expression to get the desired output.

Thanks & Regards,

Basil

Re: Evaluating Dimension Fields on both sides in Set Analysis

Nicole-Smith — Mon, 17 Mar 2014 13:23:25 GMT

Your load script should be like this (join instead of concatenate):

Sales:

LOAD ID1 as ID, AGE INLINE [

ID1,AGE

1, 10

2, 20

3, 30

];

Join

Customers:

LOAD ID2 as ID, Name INLINE [

ID2,Name

1, Amy

2, Bob

3, Carla

];

Then you can simply use ID, Name, and Age in your chart.

I've also attached a sample .qvw file.

Re: Evaluating Dimension Fields on both sides in Set Analysis

dirk_konings — Mon, 17 Mar 2014 13:53:23 GMT

I would suggert a Left Inner Join, so your ID is in both tables and mandatory in table 1.

Dirk

Re: Evaluating Dimension Fields on both sides in Set Analysis

Tue, 18 Mar 2014 08:04:43 GMT

Hi Nicole,

Thanks for your reply.

Lets say I want to keep the 2 tables concatenated due to some reasons. In this case how would you implement the same?

Thanks & Regards,

Basil

Re: Evaluating Dimension Fields on both sides in Set Analysis

Nicole-Smith — Tue, 18 Mar 2014 12:31:49 GMT

Unless I'm mistaken, you can't do it that way...

If the data isn't on the same line in your loaded table, you can't display it on the same line in a chart.

Re: Evaluating Dimension Fields on both sides in Set Analysis

dirk_konings — Thu, 20 Mar 2014 07:00:29 GMT

If you want to keep your 2 tables, you can't use SetAnalysis.

Maybe you just need to use an IF statement in your expressions?