Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Fields with the same values.

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.

8 Replies
Not applicable
Author

Hi Washington,

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

Regards.

Anonymous
Not applicable
Author

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

johnw
Champion III
Champion III

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.

Anonymous
Not applicable
Author

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

johnw
Champion III
Champion III

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.

Anonymous
Not applicable
Author

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

johnw
Champion III
Champion III

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;

Anonymous
Not applicable
Author

Beauty, I guess this will work. Thank you.