Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
washington
Creator
Creator

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

Hi Washington,

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

Regards.

washington
Creator
Creator
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
MVP
MVP

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.

washington
Creator
Creator
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
MVP
MVP

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.

washington
Creator
Creator
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
MVP
MVP

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;

washington
Creator
Creator
Author

Beauty, I guess this will work. Thank you.