10 Replies Latest reply: Sep 12, 2014 10:34 AM by pavan Kumar

# How to flag column values

Hi Friends,

I have a data set like this

A B C Code Amount

1 2 3  PC 900

1 3 4  PM  800

1 2 4   TM 800

1 2 6  PC 800

1 3 4 PM 800

Now I have decide the Amount column as sales amount, Insurance amount  and Commisions Amount based on the combinations of A B C and PC.

For example if A =1 B=2 C=3 and Code =PC then the Amount should be flagged as Sales amount.

if A=1 B=3  C=4 and Code=PM then the Amount should be flageed as Insurance amount.

• ###### Re: How to flag column values

*,

If(A=1 and B=2 and C=3 and Code = 'PC', 'SalesAmount',

If(A=1 and B=3 and C=4 and Code = 'PM', 'InsuranceAmount')) as Flag

Inline

[

A, B, C, Code, Amount

1, 2, 3, PC, 900

1, 3, 4, PM,  800

1, 2, 4, TM, 800

1, 2, 6, PC, 800

1, 3, 4, PM, 800

];

• ###### Re: How to flag column values

Hi,

if(A = 1 and B = 2 and C = 3 and Code = 'pc', '1', '0') AS [Sales Flag amount]

• ###### Re: How to flag column values

Thanks

But I have lot of combinations.

Can I use a key for combinations and flag them????

• ###### Re: How to flag column values

Left join the 3 concatenated field A&B&C as Something to a table with combinations you created.

• ###### Re: How to flag column values

Just wondering what was wrong with my answer ?

• ###### Re: How to flag column values

But Apply Map gave me easiest way to solve my problem.

• ###### Re: How to flag column values

You can create a only field having all the Flags or create individual Flag,

The best method is, use the Apply Map to create Single flag field.

FLAG_MAP:

COND, FLAG

1-2-3-PC, Sales Amount

1-2-4-PM, Insurance Amount

];

LOAD *, ApplyMap('FLAG_MAP', A&'-'&B&'-'&C&'-'&Code , 'Others') AS Amount_Type

Inline

[

A, B, C, Code, Amount

1, 2, 3, PC, 900

1, 3, 4, PM,  800

1, 2, 4, TM, 800

1, 2, 6, PC, 800

1, 3, 4, PM, 800

];

• ###### Re: How to flag column values

Hi Thanks

But I am getting blank for Amount Type in the table.

Could you please explain the 'Others' in ApplyMapt fucntion.

Thanks

Pavan

• ###### Re: How to flag column values

Hi Thanks

But I am getting blank for Amount Type in the table.

Could you please explain the 'Others' in ApplyMapt fucntion.

Thanks

Pavan

• ###### Re: How to flag column values

The reason you get blanks is because you have to use a "mapping load".

So change

FLAG_MAP: