Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Washington,
The only way that a group of fields become one is having the same field name, not the same value.
Regards.
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
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.
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
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.
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
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;
Beauty, I guess this will work. Thank you.