topic Fields with the same values. in QlikView

Fields with the same values.

Anonymous — Tue, 17 Aug 2010 13:14:07 GMT

Hello

I have a table with some fields that have the same purpose. Example:

City_1
City_2
City_3

That can occur within any of those fields have equal values.
The issue is:
As these fields become one?

City.

Fields with the same values.

Tue, 17 Aug 2010 13:19:09 GMT

Hi Washington,

The only way that a group of fields become one is having the same field name, not the same value.

Regards.

Fields with the same values.

Anonymous — Tue, 17 Aug 2010 14:39:39 GMT

I need to tally up the results in these tables:

City_1, Value City_2, Value City_3 Value

Rio de Janeiro , 10 São Paulo , 40 São Paulo , 20
Curitiba , 20 Belo Horizonte , 50 Rio de Janeiro, 30

and then return as follows:

City, sum( Value)
Rio de Janeiro 40
Curitiba 20
Sao Paulo 60
Belo Horizonte 50

Fields with the same values.

johnw — Tue, 17 Aug 2010 20:42:38 GMT

Are those really three separate tables with those field names? So you have a connection between tables on the Value field? That's nasty data design for QlikView, because it means if you select City_3 = São Paulo, it will automatically select City_1 = Curitiba for you. I really don't think there's supposed to be a relationship like this between the two cities based on value, is there?

If that is NOT your data model, well, I can't really answer your question without you giving me your actual data model.

Fields with the same values.

Anonymous — Tue, 17 Aug 2010 21:05:18 GMT

In the same table are these fields:

Select load
City_1, City_2, City_3, Value
From ...

Within the field "City" is the name of cities.

Cities need to unite the fields in order to return the total of that city in the field "Value".

City Value

Fields with the same values.

johnw — Tue, 17 Aug 2010 21:13:24 GMT

OK, thanks. Sorry about my confusion.

If only ONE of the cities is filled in, and you don't care which, I'd just combine them all into one City field like this:

LOAD
if(len(City_1),City_1,if(len(City_2),City_2,City_3)) as City
,Value
...

If more than one city can be filled in, I'll think about that case, but we'll start simple.

Fields with the same values.

Anonymous — Tue, 17 Aug 2010 21:46:15 GMT

By parts worked ...

I'm noticing that some fields are missing.

In my table all fields are filled.

City_1, City_2, City_3, Value
Rio de Janeiro São Paulo Curitiba 10

Fields with the same values.

johnw — Tue, 17 Aug 2010 22:05:11 GMT

Well, I don't know if this is really what you want, but in the attached example I do a crosstable load to get one row per city in the original row, and I associate the value with every city on that row. It's then easy to do a plain old chart with City as the dimension and sum(Value) as the expression.

RawData:
LOAD *, recno() as ID INLINE [
City_1, City_2, City_3, Value
Rio de Janeiro, São Paulo, Curitiba, 10
Rio de Janeiro, Belo Horizonte, São Paulo , 20
Curitiba, Belo Horizonte, Rio de Janeiro, 30
];

NewData:
CROSSTABLE ("City Sequence","City",2)
LOAD
ID
,Value
,City_1
,City_2
,City_3
RESIDENT RawData
;
DROP TABLE RawData;

Fields with the same values.

Anonymous — Tue, 17 Aug 2010 22:09:01 GMT

Beauty, I guess this will work. Thank you.