topic Use dimension from 2 tables?? in QlikView

Use dimension from 2 tables??

Mon, 12 May 2014 14:26:36 GMT

Hi everyone!

I´ll try to explain the problem...

I´ve got 2 tables that share very common fields, but with different aggregations and Dates, but same field names. To overcome this, I´ve used Qualify...

To associate this tables I used Date, because there isn´t any other field that it could be used. There aren´t any Key fields or ID´s to use. So, the only way I found out to connect this tables was through Date.

Now I´m facing another problem. I need to use external data to define, what I´ll call "Grand Categories" from now. These excel source tables will define "Grand Categories" based in a field present in both tables. To retrieve this information into my tables, I used Lookup function. It worked fine, but now I need to build a pivot table that use this "Grand Category" but with expressions that use fields from both tables, what I cannot do now, because OR I use "Table_A.Grand Category", OR I use "Table_B.Grand Category"...

How can I do this, without creating Synthetic Keys, or Circular References?? Use a "Generic" "Grand Category" as Dimension, and at the same time use expressions that use both tables A and B depending on the column.

Sorry if my explanation is a little bit messy, but right now I cannot send any example qvw...

Best Regards,

Bruno Silva.

Re: Use dimension from 2 tables??

Mon, 12 May 2014 15:05:29 GMT

Hi Silva, what is the business rule to define the "Grand Category" from excel. It must come from both table A & B or If A is null then use B table field?

If A table is field Null then use B Table Field else use A table field.

Then Join A with table B only add KEY_Field & B table Field. Afterthat use IF condition and derive the new temp field. After that use ApplyMap or Lookup to get the Grand Category from excel file.

Please post sample data & ur requirement.

Re: Use dimension from 2 tables??

PrashantSangle — Mon, 12 May 2014 15:14:38 GMT

Hi,

Use your dimension like,

if(isnull(Table_A.Grand Category),Table_B.Grand Category,Table_A.Grand Category)

Regards,

Re: Use dimension from 2 tables??

Mon, 12 May 2014 15:24:22 GMT

Hi! Thanks for your answer.

No, it doesn´t work like that. The "Grand Category" from Excel works like vLookup that is used on Excel itself. I have a field named Segment on both tables, and then the Lookup matches this field with "Grand Category" field and retrieves this information to each table. With this I´ve got Table_A.GrandCategory, and TableB.GrandCategory. The GrandCategory is Common to both tables, but I cannot associate the tables because it would create synthetic keys, or circular References.

The problem is that I need to use a pivot table that has columns based on metrics from table A, and other columns with metrics from table B, using "Grand Category" as Dimension . This is what I´m not beeing able to implement, because with the use of Lookup function I can indeed use "Grand Category", but only from one of the tables at a time. What I need is to used both tables "GrandCategory" at once, so that my expressions evaluate correctly.

I know I´m not explaining well... I´m sorry!

BS.

Re: Use dimension from 2 tables??

Mon, 12 May 2014 15:57:38 GMT

Hi...

Thought it was more useful, but it ain´t.

This only switch between fields, this case tables...

And what I need is that the Dimension effectively reflects tableA and tableB simultaneously.

I´ve tried to concatenate the tables maintaining the Qualify statement, Unqualifying Date and GrandCategory, but got into some other issues...

Regards,

BS.

Re: Use dimension from 2 tables??

PrashantSangle — Mon, 12 May 2014 16:06:55 GMT

Hi,

Then try the same expression in script.

Like,

If you are doing Left join on two tables

then go with resident load,

LOAD

required Field Name

then use above expression

if(isnull(Table_A.Grand Category),Table_B.Grand Category,Table_A.Grand Category) as Grand_Category

Resident tableName;

Regards

Re: Use dimension from 2 tables??

maxgro — Mon, 12 May 2014 17:04:03 GMT

why do you exclude synthetic keys?

if you have 2 common dimensions (date and grand categories) and you want to analyze in qlik charts with 2 common dimensions...........

another possibility would be to concat the 2 tables

Re: Use dimension from 2 tables??

Mon, 12 May 2014 17:18:42 GMT

Hi!

I´m excluding Synthetic Keys because this will happen with more fields. I´ve more categories that I need to aggregate. And that will lead to several synthetic keys in the document. It´s not only this one that wories me!..

I´m exploring the concatenation of the two tables, and evaluating the impacts, mostly on Data Quality!

Thanks for your answer!

Regards,

BS.