Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.


Tags (1)
1 Solution

Accepted Solutions
Not applicable

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:

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

];

10 Replies
MVP
MVP

Re: How to flag column values

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
Contributor II

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]

Not applicable

Re: How to flag column values

Thanks

But I have lot of combinations.

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

Not applicable

Re: How to flag column values

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

Not applicable

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:

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

];

Not applicable

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

Not applicable

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

geert_gelade
Contributor

Re: How to flag column values

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.

MVP
MVP

Re: How to flag column values

Just wondering what was wrong with my answer ?