Solved: Re: How to assiagn a string to group of variables - Qlik Community

Report Inappropriate Content · ‎2013-05-14

Hi All,

I have the Demograhic data with 600k+ rows. It has the Ethnicity codes like 760,745,785,746,895,852,122,685,159,125,685,268,498,267,854,958 etc...

I would like to assign

760,745,785,746,895,852,122,685 - White

159,125,685,268,498,267,854 - Asian

xxx,xxx......... - African

etc...

Could anyone advise me how I can do like this.

I have 15 ethnicity groups, each with 10+ ethnic codes

I am trying this way, but i feel it's more laborous

pick(Match(Pa_Ethnicity,'706','715','716','736','738','740','760','765','767','793','802','844','885','910','706'), 'White British','White British','White British',

'White British','White British','White British','White British','White British','White British','White British','White British','White British','White British')as PatientEthnicity

Thanks in advance

SP

Gysbert_Wassenaar · ‎2013-05-14

1. create an excel file with a table with the codes and names of the ethnic groups

2. create a mapping table

EthnicMap:

mapping load Code, Group

from ...myexcelfile...;

3. use applymap to create the PatientEthnicity field

MyPatients:

load *, applymap('EthnicMap', Pa_Ethnicity) as PatientEthnicity

from ...mysourcetable...;

talk is cheap, supply exceeds demand

View solution in original post

Report Inappropriate Content · ‎2013-05-14

I suggest you use a table for this.

You can load inline.

load *

inline [

Ethnicity code, Ethnicity

760, White

745, White

125, Asian

];

Report Inappropriate Content · ‎2013-05-14

try this, I think this helps you

firstly write

rowno() as key in your table in which this field Ethnicity code present.

example--

load Ethnicity code,

rowno() as key

from table;

Dimension-- key

Ethnicity code

Then in expression--

RangeOnly(if(key>0 and key<=8,'white',if(key>8 and key<=15,'Asian'))

Report Inappropriate Content · ‎2013-05-14

Hi Vishwarajan,

Thanks for your help.

If i use Rowno() it will return only row numbers that means 660+ row numbers. Using if condition on Key doesn't give what we want.

Is that sensible or ?

er_mohit · ‎2013-05-14

If Your Request For data to be in ascending order like 700 to 750 then 751 to 800 for your request then to assign it value it become easy instead of that you choose the selected value which also give you answer but not in one syntax as you write expression it works good but you can't do that in real world data

if your request is in meaningful way then try this

rangeonly(if(Fieldname>=700 and <= 750,'White',

rangeonly(if(Fieldname>=751 and <= 800,'British')))) as PatientEthnicity;

hope it helps

or 2nd one you have to mention it through inline load that which one is White or British or whateve you want then

With function Mapping Load and Map Using you gt the answer

hope it helps

Report Inappropriate Content · ‎2013-05-14

Many thanks for your answers.

Unfortunatly my ethnic codes are not in proper order

Forexample

706,710,715,826,842,952,456,128 codes for White British

707,708,711,827,840,451,123 codes for White Irish

etc...

Must specify/list each code for deriving ethnic group.

I will be grateful if anyone can suggest easyway to do

Here is the code that i am trying and endup getting error message (debug)

Error in expression: ')' expected

load *,

pick(Match(Pa_Ethnicity,'706','715','716','736','738','740','760','765','767','793','802','844','885','910','706',

'707','719','761','768','794','803','911',

'688','708','720','759','769','792','804','826','836','847','857','868','886','908','909',

'714','721','722','797','805','887',

'770','788','806','829','888',

'698','723','798','807','889',

'718','724','741','743','771','787','808','846','852','861','862','870','872','881','890',

'693','725','766','772','784','809','891',

'687','694','726','773','795','810','892',

'695','727','774','783','811','893',

'703','710','711','728','747','748','775','790','812','894','902',

'689','729','776','813','827','837','860','883',

'690','730','777','814','833','871',

'691','692','709','731','733','749','763','764','778','785','791','796','800','815','819',

'702','732','779','786','816','858',

'696','697','699','700','701','704','705','712','713','717','734','737','739','742','744','746','750','751','752','753','754','755','756','757','758','780','782','799','817','820',

'821','822','823','824','825','830','831','832','835','838','839','840','841','842','843','824','825','830','831','832','835','838','839','840','841','842','843','845','859','863',

'864','866','867','873','874','875','876','877','878','879','880','882','884','901','903',845','859','821','822','823','904','905','906','907',

'735','762','781','789','801','818','828','848','849','850','851','856'

), 'White British','White British','White British','White British','White British','White British','White British','White British',

'White British','White British','White British','White British','White British',

'White British','White British','White irish','White irish','White irish','White irish','White irish','White irish','White irish',

'White Any other White background','White Any other White background','White Any other White background','White Any other White background',

'White Any other White background','White Any other White background','White Any other White background','White Any other White background','White Any other White background','White Any other White background',

'White Any other White background','White Any other White background','White Any other White background','White Any other White background','White Any other White background',

'Mixed White and Black Caribbean','Mixed White and Black Caribbean','Mixed White and Black Caribbean','Mixed White and Black Caribbean','Mixed White and Black Caribbean','Mixed White and Black Caribbean',

'Mixed White and Black African','Mixed White and Black African','Mixed White and Black African','Mixed White and Black African','Mixed White and Black African',

'Mixed White and Asian','Mixed White and Asian','Mixed White and Asian','Mixed White and Asian','Mixed White and Asian',

'Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background','Mixed Any other mixed background',

'Asian or Asian British Indian','Asian or Asian British Indian','Asian or Asian British Indian','Asian or Asian British Indian','Asian or Asian British Indian','Asian or Asian British Indian','Asian or Asian British Indian',

'Asian or Asian British Pakistani','Asian or Asian British Pakistani','Asian or Asian British Pakistani','Asian or Asian British Pakistani','Asian or Asian British Pakistani','Asian or Asian British Pakistani','Asian or Asian British Pakistani',

'Asian or Asian British Bangladeshi','Asian or Asian British Bangladeshi','Asian or Asian British Bangladeshi','Asian or Asian British Bangladeshi','Asian or Asian British Bangladeshi','Asian or Asian British Bangladeshi',

'Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background','Asian or Asian British Any other Asian background',

'Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean','Black or Black British Caribbean',

'Black or Black British African','Black or Black British African','Black or Black British African','Black or Black British African','Black or Black British African','Black or Black British African',

'Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background',

'Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background','Black or Black British Any other Black background',

'Other Ethnic Groups Chinese','Other Ethnic Groups Chinese','Other Ethnic Groups Chinese','Other Ethnic Groups Chinese','Other Ethnic Groups Chinese','Other Ethnic Groups Chinese',

'Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group',

'Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group',

'Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group',

'Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group','Other Ethnic Groups Any other ethnic group',

'Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated','Other Ethnic Groups Not stated'

)as PatientEthnicity

resident Comm1;

DROP table Comm1;

Gysbert_Wassenaar · ‎2013-05-14