How to flag column values

[#1]

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.

[MK_QSL]

Load

  *,

  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

];

[narband2778]

Hi,

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

[#4]

Thanks

But I have lot of combinations.

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

[#5]

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

[#6 · Accepted solution]

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:

LOAD * INLINE [

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

];

[#7]

Hi Thanks

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

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

Thanks

Pavan

[#8]

Hi Thanks

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

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

Thanks

Pavan

[geert_gelade]

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

So change

FLAG_MAP:

LOAD * INLINE [

to

FLAG_MAP:

MAPPING LOAD * INLINE [

The 'Others' will display if no value is found in de mapping table.

[MK_QSL]

Just wondering what was wrong with my answer ?