topic Re: Club Values in a column in QlikView

Club Values in a column

Anonymous — Fri, 01 Apr 2016 10:33:22 GMT

HI All,

I Have this: Column Name= Grade

Grade:

Articles

Contract

CXO

Director

Executive

Manager

Partner

Senior Exe

Staff

Trainee

I need this :

CXO & Director as Director

M1& M2 & Manager as Manager

AM as Assistant Manager

DM as Deputy Manager

SM as Senior Manager

Senior Exe as Senior Executive

Executive

Staff

( Remove this values: Articles, Contract, Partner, Trainee)

Thanks,

Krishna

Re: Club Values in a column

robert_mika — Fri, 01 Apr 2016 10:43:57 GMT

Look for ApplyMap

Don't join - use Applymap instead

quick applymap example

Re: Club Values in a column

Kushal_Chawda — Fri, 01 Apr 2016 10:45:00 GMT

Try mapping load & apply map

Map_Grade:

LOAD

Name, Capitalize(trim(Grade) as Grade;

LOAD inline [

Grade, Name

AM, Assistant Manager

Articles,

Contract,

CXO,Director

Director,Director

DM,Deputy Manager

Executive,

M1,Manager

M2,Manager

Manager,Manager

Partner,

Senior Exe,Senior Executive

SM,

Staff,

Trainee, ];

Actualdata:

LOAD *,

applymap('Map_Grade',Grade,Grade) as GRADE

LOAD *,

capitalize(trim(Grade)) as Grade

FROM Table;

Re: Club Values in a column

Chanty4u — Fri, 01 Apr 2016 10:45:54 GMT

try this

Sample:

LOAD * INLINE [

Articles

Contract

CXO

Director

Executive

Manager

Partner

Senior Exe

Staff

Trainee

];

rrrr:

load *,

Director &'_'& CXO as Director,

M1 &'_'& M2 &'_'& Manager as Manager,

AM as [Assistant Manager],

DM as [Deputy Manager],

SM as [Senior Manager],

[Senior Exe] as [Senior Executive],

Executive,

Staff

Resident Sample;

Re: Club Values in a column

sunny_talwar — Fri, 01 Apr 2016 10:47:46 GMT

May be like this:

LOAD

If(WildMatch(Grade, 'CXO', 'Director'), 'Director',

If(WildMatch(Grade, 'M1', 'M2', 'Manager'), 'Manager',

If(WildMatch(Grade, 'AM'), 'Assistant Manager',

If(WildMatch(Grade, 'DM'), 'Deputy Manager'

If(WildMatch(Grade, 'SM'), 'Senior Manager',

If(WildMatch(Grade, 'Senior Exe'), 'Senior Executive', Grade)))))) as Grade

WHERE not Match(Grade, 'Articles', 'Contract', 'Partner', 'Trainee');

Re: Club Values in a column

Anonymous — Fri, 01 Apr 2016 10:52:41 GMT

Hi,

The table having 20 columns including Grade.

I need some values club and some values remove from Grade column.

I need to create a list box Grade in Ui as Filter.

Pls help me.

Thanks,

Krishna

Re: Club Values in a column

Kushal_Chawda — Fri, 01 Apr 2016 10:55:52 GMT

Create the field in script

LOAD

If(Match(Grade, 'CXO', 'Director'), 'Director',

If(Match(Grade, 'M1', 'M2', 'Manager'), 'Manager',

If(Match(Grade, 'AM'), 'Assistant Manager',

If(Match(Grade, 'DM'), 'Deputy Manager'

If(Match(Grade, 'SM'), 'Senior Manager',

If(Match(Grade, 'Senior Exe'), 'Senior Executive',

if(Match(Grade, 'Articles', 'Contract', 'Partner', 'Trainee'),null()))))))) as Grade

then use this field in listbox

Re: Club Values in a column

sunny_talwar — Fri, 01 Apr 2016 10:56:09 GMT

Suresh did you test this before posting it here? I am pretty sure this would error out because of the way you are calling M1 and other fields from Sample table which don't even exist.

Try testing it out before you post a response

Best,

Sunny

Re: Club Values in a column

Chanty4u — Fri, 01 Apr 2016 11:05:06 GMT

No sunny.........From next time onwards i will do the same

Re: Club Values in a column

sunny_talwar — Fri, 01 Apr 2016 11:06:53 GMT

Don't feel bad, I am just trying to help make this community a better place. I don't want you or anyone else to read a code and think, is this right?

Thanks for understanding bro

Re: Club Values in a column

Chanty4u — Fri, 01 Apr 2016 11:08:20 GMT

No worries bro I will take it as a Advice ...........Thanks