Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Can you provide the SQL that gets the Cities and States?
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))
How do you relate a city to a region?
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
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
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
Pleasure.