How can I group values on Qlikview

[#1]

Hi everyone

I'm extracting data from a server data base by ODBC connection. I want to group several cities on a same region in a state. How can I do this??

Thanks

[Jason_Michaelides]

Can you provide the SQL that gets the Cities and States?

[#3]

SQL

SELECT "AGENT_SSN",
"BEN_SEQ_NBR",
"CI_GRP_ID",
"CMS_CONTRACT",
"HCFA_PLAN",
"MBR_ADMIN_DIV_CD",
"MBR_COUNTY_NAME",
"MBR_COV_EFF_DATE",
"MBR_COV_END_DATE",
"MBR_END_REAS_CD",
"MBR_FIRST_NAME",
"MBR_LAST_NAME",
"MBR_LEGAL_ADDR1",
"MBR_LEGAL_ADDR2",
"MBR_LEGAL_ADDR3",
"MBR_MEDICARE_ID",
"MBR_PHN_AREA_CD",
"MBR_PHN_EXCH",
"MBR_PHN_STA",
"MBR_SSN",
"MBR_UMID",
"MBR_ZIP",
"PLAN_TYPE_CD",
"PROD_TYPE_CODE",
"WK-PROVIDER-ID",
"WRT_AGT_ID",
"MBR_STATUS_CD",
"WRT_AGT_LAST_NAME"
FROM "DataBase"

WHERE (CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-01-31 00:00:00', 102))OR
(CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-02-29 00:00:00', 102))OR
(CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-03-31 00:00:00', 102))OR
(CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-04-30 00:00:00', 102))OR
(CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-05-31 00:00:00', 102))OR
(CMS_CONTRACT IN ('H4007', 'H2029', 'S2874')) AND (MBR_COV_END_DATE = CONVERT(DATETIME, '2012-06-30 00:00:00', 102))

[Jason_Michaelides]

How do you relate a city to a region?

[#5]

Well, I have ir pre-selected on my work.

     The DB does not have a region field on it. I hace to create the reference. Right now I'm trying to put them as a excel file and create a relation table with the data base. In other field that I try that, It works but I don't know why with the region one don't. So analysing I think there most be a way on Qlikview that I can say like "If (something) then (something)" if that make sense. Really I'm looking for some guidance on this

[Jason_Michaelides · Accepted solution]

Ok - there is just such a function! It's called ApplyMap():

Map_Regions:

MAPPING LOAD

   City

   Region

FROM Excel...;

Data:

LOAD

   *,

   ApplyMap('Map_Regions',YourCityField,'Region Unknown') AS Region

; SQL SELECT...;

Hope this helps,

Jason

[#7]

Excellent my friend. I use your hint to search more about it and finally find the way. The SQL was pretty much like the one you wrote   Thanks a lot for you guidance.

Take Care

Danny

[Jason_Michaelides]

Pleasure.