topic Re: How can I group values on Qlikview in QlikView

How can I group values on Qlikview

Wed, 20 Jun 2012 21:57:19 GMT

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

Re: How can I group values on Qlikview

Jason_Michaelides — Wed, 20 Jun 2012 22:46:00 GMT

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

Re: How can I group values on Qlikview

Thu, 21 Jun 2012 14:00:31 GMT

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))

Re: How can I group values on Qlikview

Jason_Michaelides — Thu, 21 Jun 2012 14:14:17 GMT

How do you relate a city to a region?

Re: How can I group values on Qlikview

Thu, 21 Jun 2012 14:50:00 GMT

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

Re: How can I group values on Qlikview

Jason_Michaelides — Thu, 21 Jun 2012 17:32:12 GMT

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

Re: How can I group values on Qlikview

Thu, 21 Jun 2012 17:53:49 GMT

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

Re: How can I group values on Qlikview

Jason_Michaelides — Thu, 21 Jun 2012 18:55:06 GMT

Pleasure.